Excelで表からデータを検索する際、多くの人が真っ先に思い浮かべるのはVLOOKUP関数ではないでしょうか。しかし現在のExcel（Microsoft 365 / Excel 2021以降）には、VLOOKUPの弱点を克服したXLOOKUP関数が搭載されています。本記事では、XLOOKUP関数の基本構文から実践的な使い方、VLOOKUPとの違い、よくあるエラーの対処法までをわかりやすく解説します。
XLOOKUP関数とは
XLOOKUP関数は、指定した検索値を検索範囲から探し出し、対応する別の範囲から値を返す検索関数です。VLOOKUP・HLOOKUP・INDEX/MATCHの機能を統合したような位置づけで、2019年にMicrosoft 365で導入されました。
XLOOKUP関数の構文
=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])| 引数 | 必須 | 内容 |
|---|---|---|
| 検索値 | 必須 | 探したい値（セル参照や文字列） |
| 検索範囲 | 必須 | 検索値を探す範囲（1行または1列） |
| 戻り範囲 | 必須 | 結果として返す値がある範囲 |
| 見つからない場合 | 任意 | 該当なしの時に表示する値（省略時は#N/A） |
| 一致モード | 任意 | 0=完全一致（既定）、-1=次に小さい値、1=次に大きい値、2=ワイルドカード |
| 検索モード | 任意 | 1=先頭から検索（既定）、-1=末尾から検索、2/-2=バイナリ検索 |
基本的な使い方（実例）
以下のような社員名簿があるとします。
| A列：社員番号 | B列：氏名 | C列：部署 |
|---|---|---|
| 101 | 田中太郎 | 営業部 |
| 102 | 佐藤花子 | 経理部 |
| 103 | 鈴木一郎 | 開発部 |
社員番号「102」から部署名を取得したい場合、次のように入力します。
=XLOOKUP(102, A2:A4, C2:C4)この式は「A2:A4の中から102を探し、見つかった行のC列（部署）を返す」という意味です。結果として「経理部」が返されます。
VLOOKUPとの違い
| 比較項目 | VLOOKUP | XLOOKUP |
|---|---|---|
| 検索方向 | 検索範囲の左端列のみ | 左右どちらの列も検索範囲に指定可能 |
| 列番号の指定 | 列番号を数字で指定（列挿入で崩れやすい） | 範囲そのものを指定（列挿入に強い） |
| エラー処理 | IFERRORと組み合わせが必要 | 第4引数で直接指定可能 |
| 複数条件検索 | 補助列や配列数式が必要 | 比較的シンプルに実装可能 |
| 対応バージョン | ほぼ全バージョン | Microsoft 365 / Excel 2021以降のみ |
特に注目すべきは「列挿入への強さ」です。VLOOKUPは列番号を数値で指定するため、表に新しい列を挿入すると参照がずれてエラーの原因になります。XLOOKUPは範囲そのものを指定するため、この問題が起こりません。
複数条件・左方向検索などの応用
左方向の検索（VLOOKUPでは不可能だった処理）
先ほどの表で、逆に「部署名」から「社員番号」を検索することもできます。
=XLOOKUP("経理部", C2:C4, A2:A4)複数条件での検索
「&」で条件を連結することで、複数条件の検索が可能です。例えば「部署が営業部」かつ「氏名が田中太郎」の行を検索する場合は次のようになります。
=XLOOKUP(1, (C2:C4="営業部")*(B2:B4="田中太郎"), A2:A4)見つからない場合の表示を指定する
=XLOOKUP(999, A2:A4, C2:C4, "該当なし")検索値が見つからない場合、#N/Aエラーの代わりに「該当なし」と表示されます。
よくあるエラーと対処法
- #N/Aエラー：検索値が範囲内に存在しない場合に発生します。第4引数で代替表示を指定することで回避できます。
- #NAME?エラー：XLOOKUPが使えない古いExcelバージョンで発生します。Microsoft 365または2021以降であることを確認してください。
- #VALUE!エラー：検索範囲と戻り範囲の行数（または列数）が一致していない場合に発生します。範囲のサイズを揃えましょう。
よくある質問
Q. XLOOKUP関数はどのExcelバージョンから使えますか？
A. Microsoft 365、Excel 2021以降で使用できます。Excel 2019以前のバージョンでは利用できないため、VLOOKUPやINDEX・MATCH関数の組み合わせで代用する必要があります。
Q. XLOOKUPとVLOOKUPの一番の違いは何ですか？
A. 最大の違いは検索方向の自由度です。VLOOKUPは検索範囲の左端列しか検索できませんが、XLOOKUPは検索列と戻り値の列を別々に指定できるため、左方向の検索も可能です。列番号を数える必要がなく、列の挿入・削除にも強い点も大きなメリットです。
Q. XLOOKUPで該当データが見つからない場合はどうなりますか？
A. 第4引数の「見つからない場合」を指定しておけば、#N/Aエラーの代わりに任意の文字列（例：「該当なし」）を表示できます。この引数を省略するとVLOOKUP同様に#N/Aエラーが返されます。
まとめ
XLOOKUP関数は、VLOOKUPの弱点だった「左方向検索ができない」「列挿入に弱い」といった問題を解消した、現代のExcelにおける最強クラスの検索関数です。Microsoft 365環境であれば積極的に活用し、業務効率化に役立てましょう。
コメント