【エクセル】XLOOKUP関数の使い方を初心者向けに紹介。表から検索値を抽出する方法

初心者向けにExcelのXLOOKUP関数の使い方と手順を詳しく紹介

Excel(エクセル)のXLOOKUP関数(エックスルックアップ)は表を縦横方向に検索して検索値を抽出する関数です。

2020年2月、Microsoft365(サブスク)のアップデートで注目の新関数として登場しました。

利用出来るExcelのバージョンは、Excel2021以降とMicrosoft365と現時点では限定的です。

さらに、VLOOKUP関数やHLOOKUP関数より一段と使いやすくなった超絶便利な関数になります。

この記事では、XLOOKUP関数を初めて学ぶ人向けに、XLOOKUP関数の使い方の手順から、エラーが発生した場合の対処方法など幅広く紹介します。

エクセルのXLOOKUP関数(エックスルックアップ)とは?

XLOOKUP関数は表からデータを抽出することが可能
XLOOKUP関数は表からデータを抽出することが可能

XLOOKUP関数とは、表を縦方向や横方向に検索して情報を抽出することができる関数のことです。

XLOOKUPの「X」は縦にも横にも検索できるという意味で、「LOOKUP」は英語で探すという意味です。

VLOOKUP関数は縦方向、HLOOKUP関数は横方向にのみ検索可能でしたが、この2つの関数の足した機能を持ち合わせているのが、XLOOKUP関数です。

3つのLOOKUP関数

XLOOKUP関数・・・縦方向にも横方向にも検索可能

VLOOKUP関数・・・縦方向に検索   ※VはVertical(垂直)の頭文字

HLOOKUP関数・・・横方向に検索   ※HはHorizontal(水平)の頭文字

商品番号25の値段をXLOOKUP関数で抽出
商品番号25の値段をXLOOKUP関数で抽出

上の表では、XLOOKUP関数を使って、商品番号が『25』の値段を、指定した商品リスト表の値段列(E列)から調べて値段『1,000』円抽出しています。

つまり、指定した検索条件に『当てはまる値』を、指定した検索範囲から調べて、指定した位置から抽出することができます。

この説明だけではイメージしづらいかもしれませんが、この後に紹介するXLOOKUP関数の使い方を見れば、感覚的にどういった関数が理解出来るようになります。

まずは、いったん『表から条件に合った値を抽出することが出来る』と、思っていただいて結構です。

XLOOKUP関数の構文と引数

XLOOKUP関数の構文引数を紹介します。

構文とは、関数によって利用する引数の数や種類のことを言います。関数の書式と思いましょう。

引数とは、関数を使用するために指定するセル番号や数値、文字などのことを言います。

XLOOKUP関数の構文と引数

XLOOKUP関数の構文は下のとおりで、6つの引数を指定する必要があります。

=XLOOKUP(検索値,検索範囲,戻り範囲, [見つからない場合] ,[一致モード] ,[検索モード] )

しかし、後半の3つの引数は省略可能なため、XLOOKUP関数を普通に使う人とっては、以下の引数と理解して問題ありません。

=XLOOKUP(検索値,検索範囲,戻り範囲)

後半3つの引数の使い方は下の見出しで1つずつ紹介するよ。

XLOOKUP関数のそれぞれの引数の内容は、以下のとおりです。

第1引数:検索値
数値やセル範囲、文字列を指定できます。セルを指定せず、文字列を指定する場合は「”」で囲みます。(例:”薬”、B4)

第2引数:検索範囲
検索する範囲を指定します。検索範囲を固定したければ、絶対参照の$(ドル)を付けます。(例:B4:B15、$B$4:$B$15) 

第3引数:戻り範囲
値を抽出する範囲を指定します。範囲を固定したければ、絶対参照の$(ドル)を付けます。(例:E4:E15、$E$4:$E$15) 

第4引数:[見つからない場合] 
設定は任意です。検索値が見つからない場合は、 [見つからない場合]で指定した文字列を返します。(例:”該当なし”、”-”、”エラー”など)   

第5引数:[一致モード] 
設定は任意です。0、-1、1から選択します。
0:見つからない場合は、#N/Aが表示されます。規定の設定のため入力は不要です。
-1:見つからない場合は、次の小さな値が表示されます。
1:見つからない場合は、次の大きな値が表示されます。

第6引数:[検索モード] 
設定は任意です。-1、1から選択します。
1:先頭の項目から検索を実行します。これが規定の設定のため入力不要です。
-1:末尾の項目から検索を実行します。

XLOOKUP関数の使い方と手順を紹介

XLOOKUP関数を使って、商品リストから商品番号に合う値段を抽出
商品リストから商品番号に合う値段を抽出

それでは上の表を使って、XLOOKUP関数で「商品番号」が「25」の「値段」を抽出する方法を手順で説明します。

手順1.XLOOKUP関数を挿入

手順1.XLOOKUP関数を挿入
手順1.XLOOKUP関数を挿入

H3セルにVLOOKUP関数を挿入するため、「=XLOOKUP(」と入力します。

=XLOOKUP(

手順2.XLOOKUP関数の第1引数を入力

手順2.XLOOKUP関数の第1引数「検索値」を入力
手順2.XLOOKUP関数の第1引数「検索値」を入力

続いて第1引数「検索値」を設定するため、探したい商品番号「25」が入力されているG3セルをクリックし、「,」(カンマ)を入力します。

=XLOOKUP(G3,

G3セルを指定せず、値を直接「25」と入力してもOKだよ。

手順3.XLOOKUP関数の第2引数を入力

手順3.XLOOKUP関数の第2引数「検索範囲」を入力
手順3.XLOOKUP関数の第2引数「検索範囲」を入力

続いて、第2引数「検索範囲」を設定するため、検索値の「商品番号」列のセル範囲をマウスをドラッグして選択します。選択し終えたら、「,」(カンマ)を入力します。

=XLOOKUP(G3,B3:B8,

XLOOKUP関数を他のセルにもコピペして利用する場合は、絶対参照の「$」を付けておきましょう。

=XLOOKUP(G3,$B$3:$B$8,

絶対参照が分からない方は、以下の記事を参考にしてみてください。

手順4.XLOOKUP関数の第3引数を入力

手順4.XLOOKUP関数の第3引数「戻り範囲」を入力
手順4.XLOOKUP関数の第3引数「戻り範囲」を入力

続いて、値を抽出したいセル範囲を選択します。

第3引数「戻り範囲」を設定するため、「値段」列のセル範囲をマウスをドラッグして選択します。選択し終えたら、関数を「)」(カッコ)で閉じます。

=XLOOKUP(G3,B3:B8,E3:E8)

XLOOKUP関数を他のセルにもコピペして利用する場合は、絶対参照の「$」を付けておきましょう。

=XLOOKUP(G3,$B$3:$B$8,$E$3:$E$8)

手順5.XLOOKUP関数が完成

手順5.XLOOKUP関数が完成
手順5.XLOOKUP関数が完成

第3引数の入力が完了したら、「Enter」キーを押してXLOOKUP関数を確定させてください。

そうすると、表の中から商品番号25の値段「1000」円が抽出されました。

XLOOKUP関数を使うメリット(VLOOKUPと比較)

VLOOKUP関数を使い慣れている人にとっては、XLOOKUP関数の魅力が伝わりづらいかもしれせん。

しかしXLOOKUP関数の使いやすさを考えると、VLOOKUP関数やHLOOKUP関数を継続して使い続けるのはナンセンスです。

この見出しでは、XLOOKUP関数の特に大きなメリット3点をVLOOKUP関数と比較して紹介します。

XLOOKUP関数のメリット

1.検索範囲が左端でなくても大丈夫

2.検索値が見つからない場合の表示値を指定できる

3.引数に列番号の入力が不要

メリット1:検索範囲が左端でなくても大丈夫

VLOOKUP関数は検索値は一番左に位置する必要がある
VLOOKUP関数は検索値は一番左にないためエラー

上の表で、商品番号25の値段をVLOOKUP関数で抽出したくても、表の「商品番号」列より「値段」列の方が右に位置しています。

そのため、H3セルにVLOOKUP関数で「=VLOOKUP(G3,B3:D8,3,FALSE)」と挿入しても、エラー値「#N/A」となってしまいます。

つまり、VLOOKUP関数では、検索値は表の一番左端という制約がありました。

XLOOKUP関数は検索値が表の右側に合っても対応可能
XLOOKUP関数は検索値が表の右側に合っても対応可能

一方、XLOOKUP関数では表の右側に検索値があっても、問題なく値を抽出することが出来ます。

=XLOOKUP(G3,D3:D8,B3:B8)

メリット2:検索値が見つからない場合の表示値を指定できる

VLOOKUP関数はエラー値「#N/A」が表示される
VLOOKUP関数はエラー値「#N/A」が表示される

上の表で、商品番号「99」は無いため、以下のVLOOKUP関数で値段抽出したくても、エラー値「#N/A」が表示されてしまいます。

=VLOOKUP(G3,B3:E8,4,FALSE)

XLOOKUP関数は検索値が見つからない場合でもエラー値を回避できる
XLOOKUP関数は検索値が見つからない場合でもエラー値を回避できる

一方、XLOOKUP関数では、第4引数「見つからない場合」に任意の文字を設定しておくと、表に検索値が無い場合でも、特定の文字を返すことが出来ます。

=XLOOKUP(G3,B3:B8,E3:E8,"該当なし")

VLOOKUP関数でエラー値「#N/A」を表示させないようにするためには、IFERROR関数を利用する必要があるため、その点、XLOOKUP関数は簡単にエラー表示を回避できます。

メリット3:引数に「列番号の入力が不要

VLOOKUP関数を複数のセルに利用する場合、列番号の変更が面倒
VLOOKUP関数を複数のセルに利用する場合、列番号の変更が面倒

VLOOKUP関数の場合、上の表から、商品番号25の「商品名」だけでなく、「値段」も抽出したい場合、2つ目のVLOOKUP関数を挿入する必要があります。

その場合、1つ目のVLOOKUP関数の関数をコピペで利用しても、3つ目の引数である列番号を「3」から「4」に変更する必要があるため、数式の修正に手間がかかります。

=VLOOKUP($G$3,$B$3:$E$8,4,FALSE)

XLOOKUP関数は列番号の入力が不要
XLOOKUP関数は列番号の入力が不要

一方、XLOOKUP関数であれば、引数「列番号」を設定する必要はありません。

XLOOKUP関数に絶対参照と相対参照を上手く組み合わせて引数を設定することで、2つ目のXLOOKUP関数を簡単にコピペして利用することができます。

XLOOKUP関数で別シートや別ブックの値を抽出する方法

XLOOKUP関数の検索値や検索範囲が異なるワークシートブックに存在する場合でも関数は利用できます。

XLOOKUP関数は別シートや別ブックから値を抽出することが出来る
XLOOKUP関数は別シートや別ブックから値を抽出することが出来る

XLOOKUP関数の使い方は基本的にほぼ同じですが、シート(ブック)を切り替える手順が追加されますので操作手順を紹介します。

異なるシートで利用する場合

手順1XLOOKUP関数の第1引数を入力
XLOOKUP関数を挿入するセルに、第1引数「検索値」を入力し、末尾に”,”(カンマ)を入力します。

手順1.XLOOKUP関数を別シートで利用
手順1.XLOOKUP関数を別シートで利用

手順2.検索範囲のシートをクリック
検索範囲のデータがある「表」シートをクリックして操作するシートを切り替えします。そうすると、関数の末尾に「シート名+!」が設定されます。(例:「表!」)

=XLOOKUP(B4,表!

手順2.表シートをクリックして切り替え
手順2.表シートをクリックして切り替え

手順3.検索範囲をドラッグ
続いて第2引数「検索範囲」を設定するため、検索値である「商品番号」列から右側のセル範囲をドラッグで選択します。選択し終えたら、”,”(カンマ)を入力します。

=XLOOKUP( B4,表!B3:B8,

手順3.XLOOKUP関数の検索範囲を選択
手順3.XLOOKUP関数の検索範囲を選択

手順4.戻り範囲を選択する
続いて、抽出したい値が含まれるセル範囲を選択します。第3引数「戻り範囲」を設定するため、「値段」列のセル範囲をドラッグで選択します。選択し終えたら、「Enter」キーを押して関数を確定してください。

=XLOOKUP(B4,表!B3:B8,表!E3:E8

手順4.XLOOKUP関数の戻り範囲を選択
手順4.XLOOKUP関数の戻り範囲を選択

手順5.XLOOKUP関数を確定
関数を確定させると、XLOOKUP関数を挿入したシートに表示画面が戻ります。また、表の中から商品番号25の値段「1,000」円が抽出されます。

手順5.XLOOKUP関数が完成
手順5.XLOOKUP関数が完成

異なるExcelブックで利用する場合

異なるブックを指定する場合は、まずは2つのExcelブックを開いてください。

XLOOKUP関数の引数を設定する際に、タスクバーのExcelアイコンにマウスのカーソルを合わせて(図①)、ブックをクリックすることでブックを切り替え(図➁)することができます。

XLOOKUP関数を異なるExcelブックで利用する方法
XLOOKUP関数を異なるExcelブックで利用する方法

そうすると、上の見出しと同様の手順で、異なるExcelブックでも、XLOOKUP関数の引数を自由に選択できます。

異なるブックに対してもXLOOKUP関数の引数は設定可能
異なるブックに対してもXLOOKUP関数の引数は設定可能

=XLOOKUP(B4,[ブック①.xlsx]表!$B$3:$B$8,[ブック①.xlsx]表!$E$3:$E$8)

2つのExcelブックを立ち上げてから、XLOOKUP関数を作ってね。

XLOOKUP関数でエラー「#N/A」が表示される場合

XLOOKUP関数を使った結果がエラー値「#N/A」となるのは、検索範囲内に検索値が見つからないために発生します。

以下の3つの原因が考えられます。

エラーが発生する原因

1.XLOOKUP関数の範囲設定が相対参照

2.XLOOKUP関数の検索値がわずかに異なる

3.セルの書式が文字列

それぞれどういった内容か以下で詳しく紹介します。

XLOOKUP関数の引数が相対参照

VLOOKUP関数を他のセルにコピペして利用する際には、引数「検索範囲」に絶対参照を設定しておく必要があります。

どういうことか図解で説明します。

H3セルに挿入されているXLOOKUP関数「=XLOOKUP(G3,B3:B8,E3:E8)」を下のH4セルにコピペします。

そうすると、検索範囲内に商品番号「10」は存在するにも関わらず、XLOOKUP関数の結果は「#N/A」とエラー値が表示されてしまいます。

XLOOKUP関数をコピペするとエラーが表示される場合
XLOOKUP関数をコピペするとエラーが表示される場合

あらためてコピーしたH4セルのXLOOKUP関数を確認してみます。

そうすると、引数の「検索範囲」と「戻り範囲」が相対参照で設定されているので、1つ下に移動して、検索したい商品番号「10」がセル範囲から外れてしまっています。

=XLOOKUP(G4,B4:B9,E4:E9)

XLOOKUP関数の引数の範囲が1つ下にスライド
XLOOKUP関数の引数の範囲が1つ下にスライド

そのため、XLOOKUP関数を他のセルにもコピペで利用する場合は、引数「検索範囲」「戻り範囲」に絶対参照【$】を付けておけば、コピー先でも検索範囲が変わらないのでエラーを防ぐことが出来ます。

=XLOOKUP(G4,$B$3:$B$8,$E$3:$E$8)

XLOOKUP関数の引数に絶対参照「$」を付ける
XLOOKUP関数の引数に絶対参照「$」を付ける

XLOOKUP関数の検索値がわずかに異なる

2つ目のエラーが発生するパターンは、XLOOKUP関数の検索値と検索範囲の値がわずかに異なるために、検索値がヒットしない場合です。

例えば、「11 222」「11  222」という数値は、見た目では同じ値に見えるかもしれませんが、前者は”11”と”222”の間に半角スペースが含まれていて、後者は、”11”と”222”の間に全角スペースが含まれます。

検索値が「11 222」(半角スペース)で検索範囲の値が「11  222」(全角スペース)のため、半角スペース分異なるのでXLOOKUP関数上は違う値とみなされ、「#N/A」が表示されます。

同様に、半角スペースが先頭や末尾に入り込んでいる漢字が異なるなど、意外に陥りやすい誤りです。

XLOOKUP関数の検索値がわずかに異なるとエラー表示
XLOOKUP関数の検索値がわずかに異なるとエラー表示

セルの書式が文字列

3つ目のパターンは、検索値か検索範囲の値どちらかのセルの書式設定が【文字列】になっている場合です。

下図のように、検索値”25”は表内に存在しているので一見問題なさそうに見えますが、XLOOKUPの抽出結果は「#N/A」です。

検索範囲内に文字列が混ざっているためXLOOKUP関数でエラーが発生
検索範囲内に文字列が混ざっているためエラーが発生

原因は、B6セルの書式設定(表示形式)は【文字列】であることが原因です。

セルの書式設定(表示形式)を確認するには、対象のセルを右クリックし、右クリックメニューの「セルの書式設定」からセルの表示形式を確認してください。

文字列になっている場合は、表示形式を「標準」に変更しましょう。

セルの書式設定(表示形式)を変更する方法
セルの書式設定(表示形式)を変更する方法

XLOOKUP関数の第4引数「見つからない場合」の実例

XLOOKUP関数の第4引数[見つからない場合]を設定することで、検索値が見つからない場合のセルの表示を自在に設定することができます。

第4引数:[見つからない場合] 
設定は任意です。検索値が見つからない場合は、 [見つからない場合]で指定した文字列を返します。(例:”該当なし”、”-”、”エラー”など)   

下の図のように、第4引数に「”-”」と入力することで、XLOOKUP関数の検索値が表内に無い場合は、「-」を表示することができます。

=XLOOKUP(G3,B3:B8,E3:E8,"-")

XLOOKUP関数のエラー値を『-』で表示
XLOOKUP関数のエラー値を『-』で表示

もちろん、空白で表示したり、該当なしと表示することも出来るよ。

XLOOKUP関数の第5引数「一致モード」の実例

XLOOKUP関数の第5引数[一致モード]に「-1」もしくは「1」を設定することで、検索値が見つからない場合に、その検索値に近い値を表示することができます。

第5引数:[一致モード] 
設定は任意です。0、-1、1から選択します。
0:見つからない場合は、#N/Aが表示されます。規定の設定のため入力は不要です。
-1:見つからない場合は、次の小さな値が表示されます。
1:見つからない場合は、次の大きな値が表示されます。

下の図のように、第5引数に「-1」を設定することで、検索値「40」が表に無い場合は、その次に小さい商品番号「35」の値段「3,500」円が抽出されます。

=XLOOKUP(G3,B3:B8,E3:E8,,-1)

XLOOKUP関数の第5引数に「-1」を設定し、検索値の次に小さい値から抽出
XLOOKUP関数の第5引数に「-1」を設定し、検索値の次に小さい値から抽出

また、下の図のように、第5引数に「1」を設定することで、検索値「40」が表に無い場合は、その次に大きい品番号「78」の値段「480」円が抽出されます。

=XLOOKUP(G3,B3:B8,E3:E8,,1)

XLOOKUP関数の第5引数に「1」を設定し、検索値の次に大きい値から抽出
XLOOKUP関数の第5引数に「1」を設定し、検索値の次に大きい値から抽出

XLOOKUP関数の第6引数「検索モード」の実例

XLOOKUP関数の第6引数[検索モード]に「-1」を設定することで、末尾の項目から検索することが出来ます。

表に検索する値が複数存在する時に利用する場合に、役立ちます。

第6引数:[検索モード] 
設定は任意です。-1、1から選択します。
1:先頭の項目から検索を実行します。これが規定の設定のため入力不要です。
-1:末尾の項目から検索を実行します。

表の下側から値を取り出したい時に役立つよ

下の表のように、検索値が表の中に2つあった場合でも、第6引数に「-1」を設定することで、下側(末尾)の値を優先的に抽出することができます。

=XLOOKUP(G3,B3:B8,E3:E8,,,-1)

XLOOKUP関数の第6引数に「-1」を設定し、下側から値を抽出
XLOOKUP関数の第6引数に「-1」を設定し、下側から値を抽出

XLOOKUP関数の関連記事を紹介

最後に、XLOOKUP関数に関連した記事を2つ紹介します。

検索値が複数存在する場合の解決策(複数条件の利用)

検索値が検索範囲内に複数存在する場合、XLOOKUP関数は一番上を優先的に値を取り出します。

そのため、下の表のような場合、XLOOKUP関数で第6引数も設定せずに、検索値「15」の値段を抽出すると、一番上の「8,000」円が抽出されます。

XLOOKUP関数を複数条件で利用するためには?
XLOOKUP関数を複数条件で利用するためには?

それでは、中央の検索値「15」の値段を抽出するためにはどうすればよいでしょうか。

検索値が複数ある場合は、下の2つの手順を実施するだけで複数条件に対応出来ます。

  • 表にそれぞれの条件を結合したXLOOKUP関数用のキー列を挿入する
  • XLOOKUP関数の1つ目の引数「検索値」に”&”を利用する

D列に「商品番号」と「商品カテゴリ」の文字列を結合した「検索キー」列を挿入し、XLOOKUP関数の第1引数も「商品番号」と「商品カテゴリ」を&演算子を用いて結合します。

そして、以下のように関数を利用することで、商品番号「15」の商品カテゴリ「薬」の値段「1,000」円を抽出することが出来ます。

=XLOOKUP(H3&I3,D3:D8,F3:F8)

XLOOKUP関数を複数条件で利用する方法
XLOOKUP関数を複数条件で利用する方法

XLOOKUP関数とIF関数を使ったテクニック

XLOOKUP関数とIF関数と組み合わせることで、『表から情報を抽出し、その抽出値を条件によって別の値を変換する』ことが出来るようになります。

具体的な実例を1つご紹介します。

下の商品販売高リスト表は商品番号毎の販売高が入力されています。

セルG3にIFとXLOOKUPを組み合わせることで、セルF3の商品番号の販売高が600(千円)を越える場合は、”○”を、超えない場合は”×”と表示させることが出来ます。

=IF(XLOOKUP(F3,$B$2:$B$10,$D$2:$D$10)>=600,"○", "×" )

IF関数とXLOOKUP関数を組み合わせて販売高の〇×判定
IF関数とXLOOKUP関数を組み合わせて販売高の〇×判定

この実例の詳しい作成手順や、これ以外にもXLOOKUP関数とIF関数を組み合わせた使い方を下のリンク記事で詳しく紹介していますので、興味のある方は参考にしてみて下さい。

まとめ

今回はXLOOKUP関数の利用方法と実例をご紹介しました。

設定する範囲や値など理解しづらい点があるかもしれませんが、利用を続けていると自然と理解できるようになります。

VLOOKUP関数やHLOOKUP関数とはおさらばして、新関数XLOOKUP関数をどんどん使っていきましょう。