VLOOKUP関数を覚えたあと、「XLOOKUPという関数もあるらしい」と聞いて気になった人もいるのではないでしょうか。XLOOKUPはVLOOKUPの弱点を解消した、比較的新しい検索関数です。学校のパソコンで使えるExcelのバージョンによっては使えないこともありますが、使える環境ならぜひ覚えておきたい関数です。
この記事では、XLOOKUP関数の基本的な書き方と、VLOOKUPとの違い・メリットをわかりやすく整理します。
XLOOKUP関数とは
XLOOKUP関数は、指定した範囲の中から検索値を探し、対応する値を取り出す関数です。基本的な役割はVLOOKUPと同じですが、VLOOKUPが抱えていたいくつかの制約が解消されています。
XLOOKUP関数の書き方（引数の意味）
XLOOKUP関数は次のように書きます（[ ]は省略可能な引数です）。
=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])
| 引数 | 意味 |
|---|---|
| 検索値 | 探したい値が入っているセル |
| 検索範囲 | 検索値を探す範囲（1列だけを指定する） |
| 戻り範囲 | 取り出したい値が入っている範囲（1列だけを指定する） |
| 見つからない場合 | 該当する値が見つからなかったときに表示する内容（省略可） |
| 一致モード | 完全一致・近似値など、検索の一致条件（省略可、省略時は完全一致） |
| 検索モード | 先頭から探すか末尾から探すかなど（省略可） |
前回のVLOOKUPの記事で使った表を、XLOOKUPで書き直してみます。
| A（商品コード） | B（商品名） | C（価格） | |
|---|---|---|---|
| 1 | 商品コード | 商品名 | 価格 |
| 2 | 101 | ノート | 150 |
| 3 | 102 | 消しゴム | 80 |
| 4 | 103 | 鉛筆 | 60 |
E2セルの商品コードに対応する商品名をF2セルに表示したい場合、数式は次のようになります。
=XLOOKUP(E2, $A$2:$A$4, $B$2:$B$4, "該当なし")
「E2の値を$A$2:$A$4（商品コード列）から探し、見つかった行に対応する$B$2:$B$4（商品名列）の値を返す。見つからなければ『該当なし』と表示する」という意味です。VLOOKUPと同じく、コピーしても検索範囲や戻り範囲がズレないように絶対参照にしておく必要があります。
VLOOKUPとの違い・メリット
| VLOOKUP | XLOOKUP | |
|---|---|---|
| 検索できる列の位置 | 範囲の一番左の列だけ | 検索範囲と戻り範囲を別々に指定できるので、検索する列より左側の値も取り出せる |
| 列番号の指定 | 「範囲の中で何列目か」を数値で指定する必要がある | 戻り範囲を直接指定するので、列番号を数える必要がない |
| 見つからない場合の表示 | そのままではエラー（#N/A）になり、避けたい場合はIFERRORと組み合わせる必要がある | 「見つからない場合」の引数に直接指定できる |
| 検索方法の指定 | TRUE／FALSEで指定し、完全一致にするにはFALSEを明示する必要がある | 省略すると自動で完全一致になる |
特に大きな違いは、VLOOKUPは検索した列より左側の値を取り出せないという点です。たとえば「価格」の列から「商品コード」を探すような使い方はVLOOKUPではできませんが、XLOOKUPなら検索範囲と戻り範囲を別々に指定できるため、この制約がありません。
XLOOKUPを使うときの注意点
- XLOOKUPはExcelの比較的新しいバージョン（Microsoft 365など）でのみ使える関数です。学校や自宅のExcelのバージョンによっては使えない場合があるので、事前に確認しておきましょう
- 検索範囲・戻り範囲は、それぞれ1列（または1行）だけを指定します。VLOOKUPのように表全体をまとめて指定するわけではない点に注意してください
- 検索範囲と戻り範囲は、コピーしてもズレないように絶対参照にしておきましょう
まとめ
XLOOKUPは、VLOOKUPの「左側の列しか検索できない」「列番号を数える必要がある」「エラー処理を別に用意する必要がある」といった弱点を解消した関数です。まずはVLOOKUPの考え方をしっかり理解したうえで、XLOOKUPが使える環境であれば積極的に活用してみましょう。
以前紹介した「Excel演習ドリル」では、XLOOKUPとVLOOKUPの両方について、コピーしたときの参照の変化を選択式クイズで確認できます。この記事を読んだあとにぜひ挑戦してみてください。
VLOOKUPの基本や、参照の考え方があやふやな人は、先にこちらの記事もあわせてご覧ください。
→ VLOOKUP関数の使い方｜引数の意味と「検索方法」TRUE・FALSEの違い
→ Excelの相対参照・絶対参照の違いとは？$マークの意味をわかりやすく解説
次は、VLOOKUP・XLOOKUPのエラーを防ぐIFERROR関数について解説していきます。
コメント