Excelで作業していると、関数の名前は知っていても、実際の表に入れた瞬間に詰まることがある。範囲の大きさ、空白、文字列の扱い、Excelのバージョン差など、公式の構文だけでは見落としやすい点は多い。
この記事では、Excel XLOOKUP関数を実務の作業メモとして整理する。関数の説明だけで終わらせず、どこで使い、どこで崩れやすく、他の方法とどう使い分けるかまで見ていく。
Excel XLOOKUP関数とは
XLOOKUP関数は、指定した値を検索して対応する値を返す関数だ。VLOOKUPの後継にあたる位置づけで、検索範囲と戻り範囲を別々に指定できる点が大きな違いである。VLOOKUPで必要だった「何列目を返すか」という列番号の指定が不要になり、列の追加・削除による式崩れが起きにくい。
ただし、使えるのはMicrosoft 365またはExcel 2021以降だ。共有先のExcel環境で開くファイルに使う場合は、相手側のバージョンを先に確認しておくのが無難である。
基本構文
excel
=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])
- lookup_value（必須）：検索したい値
- lookup_array（必須）：検索する範囲（1列または1行）
- return_array（必須）：返す値がある範囲
- if_not_found（省略可）：一致しなかったときに返す値。省略すると
#N/Aが表示される - match_mode（省略可）：一致の種類。0が完全一致（省略時のデフォルト）
- search_mode（省略可）：検索の方向。1が先頭から（省略時のデフォルト）
関数名が認識されない場合は、式を直す前にExcelのバージョンや更新チャネルを確認したい。
基本例
excel
=XLOOKUP(E2,A2:A100,C2:C100,"該当なし")
E2の値をA列で検索し、一致した行のC列の値を返す。一致しない場合は「該当なし」を表示する。最初は小さい範囲で動作を確認してから実表に広げるとよい。いきなり全体に入れると、エラーが出たときに原因の切り分けが難しくなる。
実務でよく使うパターン
社員IDから氏名や部署を返す
社員IDが入力されたセル（E2）を起点に、社員マスタのID列を検索して氏名や部署を返す使い方だ。
excel
=XLOOKUP(E2,社員マスタ[社員ID],社員マスタ[氏名],"該当なし")
テーブル参照にしておくと、社員の追加・削除が起きても範囲が自動で広がるため式が崩れにくい。return_arrayを 社員マスタ[部署] に変えるだけで返す列を切り替えられるのも、VLOOKUPと比べて変更しやすい点だ。
商品コードから単価を返す
注文書や見積書など、商品コードを入力したら単価が自動で入る仕組みを作るときの基本形だ。
excel
=XLOOKUP(E2,商品マスタ[商品コード],商品マスタ[単価],"該当なし")
商品コードの書式（文字列か数値か）が検索側と一致していないと、正しく検索できないことがある。エラーが出た場合はまず元データの型を確認する。
検索列の左側にある値を返す
VLOOKUPは検索列より右の列しか返せないが、XLOOKUPは検索範囲と戻り範囲を独立して指定するため、左側の列も返せる。
excel
=XLOOKUP(E2,C2:C100,A2:A100,"該当なし")
C列で検索してA列（左側）の値を返す例だ。表の構造を変えずに左方向の検索ができるのは、XLOOKUPの実務上の利点のひとつである。
よくあるエラー・うまくいかない原因
- 関数名が認識されない：ExcelのバージョンがMicrosoft 365またはExcel 2021以降かを確認する
- lookup_arrayとreturn_arrayのサイズが合っていない：行数または列数が異なると結果が崩れる。それぞれの範囲が同じサイズになっているかを見る
- 完全一致しない：match_modeを省略すると完全一致（0）になる。近似一致やバイナリ検索を使う場合は、データが昇順・降順に並んでいる前提があることを確認する
エラーが出たときは、関数の引数だけでなく元データの状態を見るのが先決だ。空白に見えるセルにスペースが入っている、日付に見える値が文字列になっている、といった原因は実務でよく起きる。
似た関数・古いやり方との使い分け
XLOOKUPはVLOOKUPと比べて、列番号の指定が不要・左方向の検索が可能・一致しない場合の表示を引数で設定できるという点で扱いやすい。
一方、VLOOKUPはExcel 2010以降であれば使えるため、古い環境と共有するファイルでは今も現役だ。既存ファイルに組み込まれたVLOOKUPを無理に書き換える必要はない。新規で作るファイルでバージョンの問題がなければXLOOKUPを選ぶ、という使い分けで十分である。
他の関数との組み合わせ
- FILTER関数：複数条件で絞り込んだ結果をXLOOKUPに渡すより、複数行を返したい場合はFILTERの方が向いている場面もある。役割の違いを意識して使い分けるとよい
- SUMIFS関数：「一致した値を合計したい」場合はXLOOKUPではなくSUMIFSを使う。XLOOKUPは単一の値を返す関数であり、集計には向いていない
- LET関数：同じ範囲を複数回参照する式が長くなった場合、LETで中間結果に名前を付けると読みやすくなる
複数の関数を組み合わせるときは、まず「何をしたいか」を役割ごとに分けて考えるとよい。検索・抽出・集計・並べ替えをひとつの式に詰め込むと後から読みにくくなる。
まとめ
Excel XLOOKUP関数は、VLOOKUPの使いにくさを解消した検索関数だ。列番号の指定が不要で、左方向の検索にも対応している。一方で、使える環境がExcel 2021またはMicrosoft 365以降に限られるため、共有先のバージョン確認は先に済ませておきたい。
範囲・データ型・空白・バージョンまで含めて把握しておくと、式の失敗原因を切り分けやすくなる。
コメント