XLOOKUP関数の使い方｜VLOOKUPより簡単な検索関数

XLOOKUPは、表から値を探して、対応する値を返す関数です。やることはVLOOKUPと同じですが、VLOOKUPの弱点をひと通り解消した後継関数として作られています。

新しく数式を書くなら、こちらを使うほうが素直です。

書式

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

引数	内容
検索キー	探したい値
検索範囲	探しに行く1列（または1行）だけの範囲
結果範囲	返したい値が入っている範囲
見つからない場合	ヒットしなかったときに返す値。省略すると `#N/A`
一致モード	省略時は完全一致。省略でいい
検索モード	省略時は上から検索

VLOOKUPと違って、検索する列と返す列を別々に指定します。ここが最大の違いです。

基本の使い方

	A	B	C
1	商品コード	商品名	価格
2	A-101	ボールペン	150
3	A-102	ノート	320
4	B-201	ハサミ	480

「B-201」の商品名を取り出します。

=XLOOKUP("B-201", A2:A4, B2:B4)

結果：ハサミ

A列から探して、B列を返す。読んだままの意味になります。列番号を数える必要がありません。

VLOOKUPより優れている点

1. 左方向にも検索できる

VLOOKUPは範囲の左端列しか検索できません。「商品名から商品コードを引く」ことができませんでした。

XLOOKUPは検索範囲と結果範囲が独立しているので、位置関係を気にしません。

=XLOOKUP("ハサミ", B2:B4, A2:A4)   → B-201

2. 列を挿入しても壊れない

VLOOKUPは「左から3番目」という位置で指定するため、表の途中に列を挿入すると数式が別の列を指してしまいます。

XLOOKUPは範囲そのものを指定するので、列を挿入しても範囲が自動で追従します。運用中に壊れないのが実務では大きいです。

3. 見つからないときの表示を指定できる

VLOOKUPでは IFERROR で包む必要がありました。

=IFERROR(VLOOKUP(E2, A:C, 2, FALSE), "該当なし")

XLOOKUPは4つ目の引数で直接指定できます。

=XLOOKUP(E2, A:A, B:B, "該当なし")

IFERROR は数式全体のエラーを握りつぶすので、範囲指定のミスまで隠してしまいます。XLOOKUPの4つ目の引数は「見つからなかった場合」だけを扱うため、他のエラーはちゃんとエラーとして表面化します。この違いは地味ですが重要です。

4. 複数の列をまとめて返せる

結果範囲を複数列にすると、その列数ぶんが一度に返ります。

=XLOOKUP("B-201", A2:A4, B2:C4)   → ハサミ | 480

商品名と価格が横並びで一度に出ます。VLOOKUPだと数式を2つ書く必要がありました。

下から検索する

同じ検索キーが複数ある場合、通常は最初に見つかったものが返ります。「最新の1件が欲しい」ときは6つ目の引数に -1 を指定します。

=XLOOKUP(E2, A:A, B:B, "", 0, -1)

日付順に追記していく履歴表から最新の状態を取り出す、という使い方ができます。

注意点

古いExcelでは使えません。 スプレッドシートでは問題なく使えますが、Excelで開く可能性のあるファイルでは注意が必要です。Microsoft 365 と Excel 2021 以降でのみ対応しています。取引先とファイルをやり取りする場合は、VLOOKUP のままにしておくほうが無難な場面もあります。

検索範囲と結果範囲の行数は揃える必要があります。 A2:A100 と B2:B50 のようにずれていると #VALUE! になります。

VLOOKUPからの置き換えで詰まるところ

XLOOKUPはVLOOKUPの上位互換として紹介されることが多いのですが、引数の考え方が根本的に違うため、慣れるまでは戸惑います。違いを整理しておくと移行が早くなります。

最大の違いは、範囲の指定の仕方です。VLOOKUPは「検索する列を含んだ表全体」をひとつの範囲として渡し、そこから何列目を返すかを数字で指定しました。XLOOKUPは「検索する列」と「返す列」を別々に指定します。数字を数える必要がないので、列を挿入しても壊れません。VLOOKUPで最も多い事故が構造的に起きなくなる、というのが実務上いちばん大きな利点です。

次の違いは、完全一致が既定であることです。VLOOKUPは第4引数を省略すると近似一致になり、意図しない値が返る事故が起きていました。XLOOKUPは何も指定しなければ完全一致で動きます。省略して安全なほうに倒れる設計になっています。

三つ目は、見つからないときの処理が組み込まれていることです。VLOOKUPではIFERRORで包む必要がありましたが、XLOOKUPは引数のひとつとして「見つからないときに返す値」を渡せます。数式が短くなるだけでなく、IFERRORで本当のエラーまで隠してしまう危険も避けられます。

四つ目は、検索する方向を選べることです。上から探すか下から探すかを指定できるため、同じキーが複数ある表から最新の1件を取り出す、といった処理が単独でできます。VLOOKUPでは常に一番上の行しか取れませんでした。

複数の列をまとめて返す

XLOOKUPの機能で見落とされやすいのが、返す範囲に複数列を指定できる点です。商品コードから商品名と単価と在庫数を一度に取り出したい場合、VLOOKUPなら3本の数式を書くことになりますが、XLOOKUPなら1本で済みます。

返す範囲を複数列にすると、結果が横方向にあふれて表示されます。数式を入れたセルの右側が空いている必要があるので、あらかじめ空けておいてください。既に何か入っていると、あふれられずにエラーになります。

この書き方には速度面の利点もあります。検索の処理が1回で済むためです。行数の多い表で3本のVLOOKUPを書くと、同じ検索を3回繰り返すことになります。列数が増えるほど差が出ます。

同じ考え方で、行方向に返すこともできます。横に並んだ表から該当する列を丸ごと取り出したい場合、HLOOKUPを使わずにXLOOKUPで書けます。検索範囲と返す範囲の向きさえ揃っていれば、縦でも横でも同じように動きます。

一致モードを使い分ける

XLOOKUPには一致の仕方を指定する引数があり、これを理解すると使える場面が広がります。

既定は完全一致です。日常の検索はこれで足ります。

次に、次に小さい値または次に大きい値を探すモードがあります。料金表のように「◯円以上◯円未満」で区分が変わる表では、これを使います。VLOOKUPの近似一致に相当しますが、昇順に並んでいなくても動くという違いがあります。並べ替えを前提にしなくてよいぶん、事故が減ります。

さらに、ワイルドカードを使うモードがあります。部分一致で検索したい場合に指定します。既定ではワイルドカードは効かないので、明示的に有効にする必要があります。ここを知らないと「アスタリスクを書いたのに効かない」と悩むことになります。

検索の方向を指定する引数も別にあります。既定は先頭から末尾に向かって探しますが、逆向きを指定すれば末尾から探します。履歴の表から最新のレコードを取り出す用途では、これが決定的に便利です。

使えない環境での代替

XLOOKUPは比較的新しい関数のため、古いExcelでは使えません。ファイルを他の人と共有する場合、相手の環境で開けるかどうかを先に確認してください。XLOOKUPを含むファイルを古い環境で開くと、数式がエラーとして表示されます。

代替としては、INDEXとMATCHの組み合わせが定番です。どの環境でも動き、左方向の検索もできます。書き方は少し長くなりますが、考え方はXLOOKUPと同じで「検索する列」と「返す列」を別に指定します。→ INDEX関数の使い方／ MATCH関数の使い方

社内で共有するファイルは、相手の環境に合わせて関数を選ぶという判断が必要になります。自分の環境で動くことと、配布先で動くことは別の話です。

検索を含むシートの設計

検索の関数を多用するシートでは、設計次第で保守性と速度が大きく変わります。

まず、マスタの置き場所を決めます。専用のシートを一枚用意し、そこにすべてのマスタを置きます。商品マスタ、取引先マスタ、区分の対応表。散らばっていると、どこを直せばよいか分からなくなります。

次に、マスタの範囲に名前を付けます。名前を付けておけば、数式が読みやすくなり、範囲の変更も一箇所で済みます。数式を見たときに、何を参照しているかが名前から分かります。

三つ目に、検索の回数を減らします。同じ検索を複数の列で繰り返すより、複数列を一度に返す書き方を使うほうが速くなります。あるいは、作業列に一度だけ検索結果を持たせて、以降はその列を参照します。

四つ目に、検索範囲を必要な部分に絞ります。列全体を指定すると、行数の多いシートでは負荷がかかります。

五つ目に、見つからない場合の扱いを決めておきます。未登録として表示するのか、空欄にするのか。表全体で統一してください。

この五点を最初に決めておけば、シートが大きくなっても管理できます。

マスタの整備が精度を決める

検索が正しく動くかどうかは、マスタの状態で決まります。数式をいくら工夫しても、マスタが汚れていれば一致しません。

キーとなる列に重複がないか確認してください。同じコードが複数行あると、一番上の行だけが返ります。意図しない値が返り続けることになります。件数を数える関数で、二以上のものがないかを確認できます。

前後の空白が入っていないか確認してください。取り込んだデータでは頻繁に混入します。文字数を数えれば分かります。

型が揃っているか確認してください。数値であるべき列に文字列が混ざっていると、一致しません。数値だけを数えた件数と全体の件数を比べれば分かります。

表記が統一されているか確認してください。全角と半角、大文字と小文字、旧字体と新字体。マスタの側で統一しておけば、検索する側でも揃えるだけで済みます。

これらの確認を、マスタのシートに常設しておくことをおすすめします。件数と重複と型の確認を上部に並べておけば、マスタを更新するたびに状態が見えます。

マスタが汚れると、それを参照するすべての処理が影響を受けます。整備の優先度は高く見積もってください。

よくある質問

Q. VLOOKUPで作った既存のファイルは書き換えるべきですか

動いているものを急いで書き換える必要はありません。ただし、列の挿入で壊れた経験があるファイルは、その部分だけでも置き換える価値があります。

Q. 結果があふれてエラーになります

複数列を返す指定になっていて、右側のセルが埋まっている状態です。空けるか、返す範囲を1列に絞ってください。

Q. 見つからないときの表示を空欄にしたいのですが

見つからないときの値を指定する引数に、空文字を渡してください。IFERRORで包む必要はありません。

Q. 大文字と小文字は区別されますか

区別されません。区別したい場合は、EXACT関数と組み合わせた別の書き方が必要になります。

Q. 検索範囲と返す範囲の行数が違うとどうなりますか

エラーになります。両方の範囲は必ず同じ行数にしてください。 片方だけ範囲を広げてしまう間違いがよくあります。

Q. 複数の条件で検索できますか

検索範囲の指定をアンパサンドで連結する書き方があります。ただし読みにくくなるので、マスタ側に結合列を作るほうが保守しやすくなります。

Q. スプレッドシートでも使えますか

使えます。書き方も同じです。ただし、古いファイルを共有する相手の環境によっては表示されないことがあります。

Q. VLOOKUPより速いですか

同じ処理を1本で書ける分だけ有利ですが、劇的に速くなるわけではありません。速度が問題になる規模なら、範囲の指定を見直すほうが効果があります。

まとめ

書式は =XLOOKUP(検索キー, 検索範囲, 結果範囲, 見つからない場合)
列番号を数えなくていい
左方向にも検索できる
列の挿入で壊れない
見つからないときの値を直接指定できる

新規に作るならXLOOKUP。既存ファイルの保守や、古いExcelとの互換が要るならVLOOKUP。詳しい使い分けは VLOOKUPとXLOOKUPの違いにまとめました。