VLOOKUPの「あるある」は、XLOOKUPでほとんど解消できます
「列番号を数え間違えて、別の値が出ていた」「列を挿入したら、数式がずれた」。VLOOKUPを使ったことがある人なら、一度は経験しているのではないでしょうか。
XLOOKUPは、こうしたVLOOKUPの弱点を補うために作られた関数です。この記事では、両者の違い、基本の使い方、既存のファイルを置き換える手順を順に解説します。
VLOOKUPとXLOOKUPの違い
XLOOKUPでは、「どこを探すか」と「どこから取り出すか」を別々の範囲で指定します。この仕組みの違いが、次の差につながっています。
| 比較点 | VLOOKUP | XLOOKUP |
|---|---|---|
| 取り出す列の指定 | 左から何列目かを数字で指定 | 取り出したい列をそのまま選ぶ |
| 検索できる向き | 検索列より右側だけ | 左右どちらでも |
| 列を挿入・削除したとき | 列番号がずれ、結果が変わる | 範囲が自動で追従する |
| 検索方法を省略したとき | 近似一致（意図しない値が出ることがある） | 完全一致 |
| 見つからないとき | #N/A（IFERRORで囲む必要がある） | 表示する文字を関数の中で指定できる |
| 使えるExcel | ほぼすべてのバージョン | Microsoft 365、Excel 2021以降など |
XLOOKUPの基本の使い方
必須なのは最初の3つだけです。「何を」「どこから探して」「どこから取り出すか」の順に指定します。
=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])| 引数 | 意味 | 必須？ |
|---|---|---|
| 検索値 | 探したい値（例：商品コード） | 必須 |
| 検索範囲 | 検索値を探す列 | 必須 |
| 戻り範囲 | 結果として取り出す列 | 必須 |
| 見つからない場合 | 該当がないときに表示する値 | 任意 |
| 一致モード | 完全一致・近似一致などの指定（省略すると完全一致） | 任意 |
| 検索モード | 上から探すか、下から探すかなどの指定 | 任意 |
例：商品コードから単価を取り出す
次のような商品マスタがあるとします（A列：商品コード、B列：商品名、C列：単価）。
E2セルに入力した商品コードから単価を取り出す式は、次のとおりです。
E2に「A002」と入力すると「120」が表示されます。マスタにないコードを入れたときは、「該当なし」と表示されます。
VLOOKUPでは難しかった3つのこと
- 検索列より左側の値を取り出す
VLOOKUPでは、商品名から商品コード（左の列）を取り出せません。そのため、列の並び替えやINDEXとMATCHの組み合わせが必要でした。XLOOKUPなら、範囲を入れ替えるだけです（F2セルに商品名を入力した場合）。
- 複数の列をまとめて取り出す
戻り範囲を複数列にすると、商品名と単価を一度に表示できます。結果は右隣のセルまで自動で広がります。VLOOKUPのように、列ごとに式を書き分ける必要はありません。
- 列を挿入しても結果がずれない
VLOOKUPは「左から3列目」のように列を数字で指定するため、列を挿入すると別の列を参照してしまいます。XLOOKUPは列そのものを指定しているので、挿入しても範囲が自動でずれ、正しい値を取り出し続けます。
VLOOKUPからの置き換え手順
既存のファイルを書き換えるときは、次の順に進めると安全です。
- ファイルをコピーする：元のファイルは残し、コピーした方で作業します。
- VLOOKUPの式を分解する：「検索値」「検索する列」「取り出す列」の3つに分けて考えます。
- XLOOKUPに書き換える：列番号の代わりに、取り出す列の範囲を指定します。
- 結果を見比べる：隣の列に新しい式を入れ、元の式と同じ値が出るか確認します。
- 古い列を削除する：すべて一致したら、VLOOKUPの列を削除します。
たとえば、次のVLOOKUPの式は……
=VLOOKUP(E2, $A$2:$C$100, 3, FALSE)次のXLOOKUPの式に置き換えられます。「3列目」だったところが、C列の範囲に変わっています。
=XLOOKUP(E2, $A$2:$A$100, $C$2:$C$100)注意したいのは、元の式が「TRUE」や省略（近似一致）になっている場合です。そのまま置き換えると完全一致になり、結果が変わることがあります。点数から評価を決める表など、意図して近似一致を使っている式は、一致モードも合わせて指定してください。
置き換える前に確認したいこと
XLOOKUPは、すべてのExcelで使えるわけではありません。
- 使えるバージョン：Microsoft 365、Excel 2021以降、Web版Excelで使えます。Excel 2019以前では使えません。
- ファイルを渡す相手の環境：古いExcelで開くと、XLOOKUPのセルはエラーになります。取引先や他部署と共有するファイルは、相手のバージョンを確認してから置き換えましょう。
- すべてを一度に書き換えなくてよい：動いているVLOOKUPはそのままでも問題ありません。新しく作る式や、列をよく挿入する表から切り替えるのが現実的です。
GoogleスプレッドシートでもXLOOKUP関数を使えます。
まとめ
XLOOKUPを使えば、列番号の数え間違いや列の挿入によるずれを気にする必要がなくなります。まずは次に作る表で、VLOOKUPの代わりに1つ使ってみてください。
関数の入力をさらに速くしたい方には、デスクトップ画像としても使える「Excelショートカット一覧」を無料で配布しています。
この記事は役に立ちましたか？下の星で評価していただけると、今後の記事づくりの参考にします。
コメント