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-2012. 列を挿入しても壊れない
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! になります。
まとめ
- – 書式は
=XLOOKUP(検索キー, 検索範囲, 結果範囲, 見つからない場合) - – 列番号を数えなくていい
- – 左方向にも検索できる
- – 列の挿入で壊れない
- – 見つからないときの値を直接指定できる
新規に作るならXLOOKUP。既存ファイルの保守や、古いExcelとの互換が要るならVLOOKUP。詳しい使い分けは VLOOKUPとXLOOKUPの違い にまとめました。
コメントを残す