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

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

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

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

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

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

エクセルのXLOOKUP関数(エックスルックアップ）とは？

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

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

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

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

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

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

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

XLOOKUP関数の構文と引数

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

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

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

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

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

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

＝XLOOKUP(検索値,検索範囲,戻り範囲）

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

私

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

第１引数：検索値
数値やセル範囲、文字列を指定できます。セルを指定せず、文字列を指定する場合は「”」で囲みます。（例：”薬”、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関数で「商品番号」が「25」の「値段」を抽出する方法を手順で説明します。

手順１．XLOOKUP関数を挿入

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

＝XLOOKUP(

手順２．XLOOKUP関数の第１引数を入力

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

＝XLOOKUP(G3,

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

私

手順３．XLOOKUP関数の第２引数を入力

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

=XLOOKUP(G3,B3:B8,

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

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

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

手順４．XLOOKUP関数の第３引数を入力

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

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

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

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

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

手順５．XLOOKUP関数が完成

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

メリット３：引数に「列番号」の入力が不要

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

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

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

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

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

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

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

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

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

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

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

＝XLOOKUP(B4,表!

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

＝XLOOKUP( B4,表!B3:B8,

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

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

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

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

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

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

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

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

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

私

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

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

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

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

XLOOKUP関数の引数が相対参照

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

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

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

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

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

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

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

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

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

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

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

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

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

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

セルの書式が「文字列」で、数値として扱われていない

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

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

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

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

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

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

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

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

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

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

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

私

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

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

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

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

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

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

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

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

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

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

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

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

私

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

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

XLOOKUP関数の関連記事を紹介

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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