Excelで表から値を探して取り出すとき、長く定番だったのがVLOOKUP関数です。
現在はその弱点を解消したXLOOKUP関数が使えるようになり、実務でも資格試験の学習でも欠かせない関数になっています。
この記事では、XLOOKUP関数の基本的な使い方とVLOOKUPとの違い、MOS Excelエキスパートの学習で押さえたいポイントを解説します。
XLOOKUP関数とは
XLOOKUP関数は、指定した値を検索範囲から探し、同じ位置にある別の列（または行）の値を返す関数です。
商品コードから商品名や単価を取り出す、社員番号から氏名を表示するといった場面でよく使われます。
XLOOKUPを使えるのは、Microsoft 365とExcel 2021以降、Web版のExcelです。
Excel 2019以前では使えないため、古いバージョンで開くとエラーになる点に注意しましょう。
XLOOKUP関数の書式と引数
書式は次のとおりです。
=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])
| 引数 | 内容 | 省略 |
|---|---|---|
| 検索値 | 探したい値（例：商品コード） | 不可 |
| 検索範囲 | 検索値を探す列または行 | 不可 |
| 戻り範囲 | 取り出したい値がある列または行 | 不可 |
| 見つからない場合 | 一致しないときに表示する値 | 可 |
| 一致モード | 完全一致・近似一致・ワイルドカードなど | 可（既定は完全一致） |
| 検索モード | 先頭から・末尾からなどの検索方向 | 可（既定は先頭から） |
最低限、最初の3つを指定すれば使えます。
一致モードの既定値が完全一致なので、VLOOKUPのように最後に「FALSE」を付け忘れて誤った値が返る心配がありません。
基本の使い方
商品コードから単価を取り出す
A列に商品コード、B列に商品名、C列に単価が入った表があるとします。
セルF2に入力した商品コードの単価を表示したい場合は、次のように入力します。
=XLOOKUP(F2, A2:A100, C2:C100)
検索範囲と戻り範囲を別々に指定するので、何列目かを数える必要がありません。
見つからない場合のメッセージを表示する
存在しないコードを入力すると、そのままではエラーが表示されます。
4番目の引数を使えば、エラーの代わりに好きな文字を表示できます。
=XLOOKUP(F2, A2:A100, C2:C100, “該当なし”)
VLOOKUPでは IFERROR関数と組み合わせる必要がありましたが、XLOOKUPなら1つの関数で済みます。
左側の列の値を取り出す
VLOOKUPは検索列より左にある値を取り出せませんでしたが、XLOOKUPは戻り範囲を自由に指定できます。
たとえば、C列の単価からA列の商品コードを探すといった使い方も可能です。
VLOOKUPとXLOOKUPの違い
| 比較項目 | VLOOKUP | XLOOKUP |
|---|---|---|
| 取り出す列の指定 | 左から何列目かを数値で指定 | 戻り範囲を直接指定 |
| 左方向の検索 | できない | できる |
| 既定の一致方法 | 近似一致（FALSE指定が必要） | 完全一致 |
| 見つからない場合 | IFERRORと組み合わせる | 引数で指定できる |
| 列の挿入・削除 | 列番号がずれて結果が変わることがある | 範囲指定なので影響を受けにくい |
| 使えるバージョン | ほぼすべてのバージョン | Microsoft 365・Excel 2021以降 |
新しく作るファイルで、利用者全員がMicrosoft 365やExcel 2021以降を使っているなら、XLOOKUPを選ぶのがおすすめです。
一方、取引先など古いバージョンを使う相手とファイルをやり取りする場合は、VLOOKUPやINDEX関数とMATCH関数の組み合わせを使うほうが安全です。
MOS Excelエキスパート対策のポイント
MOS Excel 365のエキスパートレベルでは、高度な数式の分野で検索用の関数が扱われ、XLOOKUPのほかVLOOKUP、HLOOKUP、MATCH、INDEXなどが対象になります。
出題範囲の細目は改定されることがあるため、受験前にMicrosoftの公式ページで最新の出題範囲を確認しておきましょう。
試験では、関数名を知っているだけでなく、指示どおりの範囲を正確に指定できるかが問われます。
- 検索範囲と戻り範囲の行数（列数）をそろえる
- 数式をコピーする場合は、範囲を絶対参照にする
- 「見つからない場合」の指定を求められたら第4引数を使う
- VLOOKUPとINDEX・MATCHでも同じ結果を出せるよう練習する
特に絶対参照の付け忘れは、コピーした先で範囲がずれる典型的なミスです。
対策テキストの模擬試験で、関数の入力から数式のコピーまでを一連の操作として練習しておくと安心です。
よくある質問
Q. XLOOKUPを入力したら#NAME?エラーになりました。
A. お使いのExcelがXLOOKUPに対応していない可能性があります。
Excel 2019以前では使えないため、バージョンを確認するか、VLOOKUPやINDEX・MATCHで代用してください。
Q. MOS Excelの一般レベルでもXLOOKUPは出題されますか？
A. 検索用の関数は主にエキスパートレベルで扱われる内容です。
一般レベルを受験する場合も、実務で役立つ関数なので基本的な使い方は覚えておくと便利です。
Q. VLOOKUPはもう覚えなくていいですか？
A. 古いバージョンとのファイル共有や、既存の資料の修正ではVLOOKUPがまだ多く使われています。
両方の使い方を理解しておくと、職場でも試験でも困りません。
