Excelで商品名から単価を調べたい、社員番号から部署名を抽出したい、別シートにある一覧から必要な情報だけを表示したいという場面では、XLOOKUP関数が便利です。
従来のVLOOKUP関数やINDEX関数とMATCH関数の組み合わせに比べて、検索範囲と戻り値の範囲を分けて指定できるため、表の右から左へ検索するケースにも対応しやすくなっています。
XLOOKUP関数は、検索したい値を見つけて、同じ行または列にある関連データを返す関数です。
基本の考え方は、検索値を探し、検索範囲内で一致した位置から、戻り値の範囲にある値を抽出することです。
商品コード、氏名、社員番号、顧客IDなど、重複しにくいキーを用意すると使いやすくなります。
この記事では、XLOOKUP関数の基本構文、検索方法、複数列の抽出、見つからない場合の処理、VLOOKUP関数との違いまで順番に解説します。
サンプルデータは、1行目に見出しが入力されているものとして説明します。
XLOOKUP関数の基本入力
それではまず、XLOOKUP関数を使って商品コードから商品名と単価を検索・抽出する基本操作について解説していきます。
| A列 | B列 | C列 | D列 |
|---|---|---|---|
| 商品コード | 商品名 | 単価 | 在庫数 |
| P001 | ノート | 180 | 35 |
| P002 | ボールペン | 120 | 48 |
| P003 | クリアファイル | 90 | 62 |
基本構文と引数の役割
XLOOKUP関数の基本構文は、XLOOKUP(検索値,検索範囲,戻り値の範囲,見つからない場合)です。
=XLOOKUP(F2,$A$2:$A$4,$B$2:$B$4,”該当なし”)
この数式では、F2セルの商品コードをA2からA4の範囲で探し、見つかった行にあるB列の商品名を返します。
検索範囲と戻り値の範囲は、同じ行数または同じ列数にそろえることが重要です。
F2にP002と入力されていれば、数式を入力したセルにはボールペンと表示されます。
VLOOKUP関数のように列番号を数える必要がないため、列を追加した後も数式が崩れにくい点が特徴です。
【操作のポイント】検索値は入力ミスを防ぐため、商品コード一覧を使った入力規則のリストにすると安定します。
検索用セルと数式の入力
実務では、検索したいコードを入力するセルと、抽出結果を表示するセルを分けて作成します。
たとえばF1に検索する商品コード、G1に商品名という見出しを入力し、F2にP001などのコードを入力します。
次にG2セルを選択し、数式バーまたはセル内にXLOOKUP関数を入力しましょう。
検索範囲は商品コードが入力されたA2からA4、戻り値の範囲は商品名が入力されたB2からB4です。
範囲を指定するときは、F4キーで絶対参照にしておくと、後から数式をコピーする場合にも参照先がずれません。
検索値だけを相対参照にし、一覧表の検索範囲と戻り値の範囲を絶対参照にするのが基本です。
数式を確定すると、検索コードを変更するたびに抽出結果も自動更新されます。
【操作のポイント】検索セルと結果セルを隣接させると、利用者が入力場所と表示内容を把握しやすくなります。
見つからない場合の表示設定
検索値が一覧に存在しないとき、4番目の引数を省略すると、通常は#N/Aエラーが表示されます。
入力途中のコードや未登録の商品コードがある運用では、エラー表示をそのまま見せない設定が役立ちます。
=XLOOKUP(F2,$A$2:$A$4,$B$2:$B$4,”商品コードを確認してください”)
このように4番目の引数へ文章を指定すると、一致しない場合だけ指定したメッセージを表示できます。
IFERROR関数で全体を囲むよりも、XLOOKUP関数の見つからない場合の引数を使うほうが、意図が読み取りやすい数式になります。
ただし、数式の構文ミスまで隠す設定ではないため、データ範囲や参照先の確認も必要です。
【操作のポイント】未登録時の表示は空白よりも、確認を促す文章にすると入力ミスに気付きやすくなります。
完全一致と部分一致の検索設定
続いては、XLOOKUP関数で完全一致、ワイルドカードを使った部分一致、近似一致を使い分ける方法を確認していきます。
| A列 | B列 | C列 |
|---|---|---|
| 社員番号 | 氏名 | 部署 |
| 1001 | 田中花子 | 営業部 |
| 1002 | 佐藤健 | 総務部 |
| 1003 | 鈴木美咲 | 開発部 |
完全一致による正確な照合
商品コード、社員番号、注文番号のように、一意の値を照合する場合は完全一致で検索します。
XLOOKUP関数は、4番目以降の引数を省略したとき、通常は完全一致として動作します。
=XLOOKUP(F2,$A$2:$A$4,$C$2:$C$4,”該当なし”,0)
5番目の引数に0を指定しても完全一致です。
コード検索では、似た値を返す近似一致ではなく、完全一致を基本にすることが安全です。
たとえば100と1000が混在するデータで誤った結果を返さないためにも、検索キーの表記を統一しておきましょう。
【操作のポイント】先頭のゼロを含むコードは、検索値側と一覧側の両方を文字列として統一します。
ワイルドカードによる部分一致
氏名や商品名の一部から候補を探す場合は、アスタリスクを使ったワイルドカード検索を利用できます。
たとえばF2セルに田中と入力し、田中を含む氏名を検索する場合には、検索値の前後へアスタリスクを付けます。
=XLOOKUP(“*”&F2&”*”,$B$2:$B$4,$C$2:$C$4,”該当なし”,2)
5番目の引数の2は、ワイルドカード文字を利用した一致を指定する値です。
部分一致では、複数件が該当しても最初に見つかった1件だけが返されます。
候補をすべて一覧表示したい場合は、XLOOKUP関数ではなくFILTER関数を検討する必要があります。
【操作のポイント】部分一致検索は便利ですが、同姓の社員や似た商品名がある場合は検索結果を必ず確認します。
近似一致による区分の判定
売上金額からランクを返す、点数から評価を表示するといった区分判定では、近似一致を使うことがあります。
この場合、検索範囲は基準値を小さい順または大きい順に整理しておくことが大切です。
=XLOOKUP(F2,$A$2:$A$5,$B$2:$B$5,”該当なし”,-1)
5番目の引数に-1を指定すると、完全一致がない場合に次に小さい値を検索します。
近似一致は並び順と基準値の設計によって結果が変わるため、コード検索とは別の用途として考えましょう。
判定表の境界値を明確にし、実際の数値でテストすることが欠かせません。
【操作のポイント】近似一致の判定表は、数値の並び順と境界条件を表の横にメモしておくと保守しやすくなります。
複数列データの抽出
続いては、XLOOKUP関数で商品名、単価、在庫数など複数列のデータをまとめて抽出する方法を確認していきます。
| 検索セル | 抽出結果 | 抽出結果 | 抽出結果 |
|---|---|---|---|
| F2 P002 | G2 商品名 | H2 単価 | I2 在庫数 |
| 検索キー | ボールペン | 120 | 48 |
戻り値の範囲を複数列にする方法
戻り値の範囲にB列からD列までを指定すると、1つの数式で商品名、単価、在庫数を横方向に抽出できます。
=XLOOKUP(F2,$A$2:$A$4,$B$2:$D$4,”該当なし”)
数式はG2セルに入力します。
一致したP002の行から、B列からD列にあるボールペン、120、48がG2からI2へ自動的に展開されます。
複数列を返すXLOOKUP関数では、結果が広がる先のセルを空けておく必要があります。
展開先に値や数式が入っていると、スピル範囲エラーになる場合があります。
【操作のポイント】結果の右側に入力済みセルがないか確認してから、複数列を返す数式を設定します。
数式のコピーとオートフィル
検索コードが複数行に並ぶ一覧では、先頭行だけに数式を入力してからオートフィルで下方向へコピーします。
たとえばF2からF10に商品コードがある場合、G2へ数式を入力し、セル右下のフィルハンドルを下へドラッグします。
検索範囲と戻り値の範囲を絶対参照にしておけば、オートフィル後も一覧表の参照先は固定されます。
検索値のF2だけはF3、F4と行に合わせて変化するため、各行のコードに対応した情報が抽出されます。
=XLOOKUP(F2,$A$2:$A$100,$B$2:$D$100,”該当なし”)
元データが今後増える場合は、Excelのテーブル機能を利用して検索範囲を管理する方法も便利です。
【操作のポイント】オートフィル前に最初の数式が正しい結果を返していることを確認すると、誤りを一括コピーせずに済みます。
別シート参照によるマスタ検索
商品マスタを別シートに置き、入力用シートから検索する構成は、データ管理の基本です。
商品マスタというシート名なら、検索範囲の前に商品マスタ!を付けて参照します。
=XLOOKUP(F2,商品マスタ!$A$2:$A$100,商品マスタ!$B$2:$D$100,”未登録”)
シート名に空白が含まれる場合は、シート名を単一引用符で囲みます。
入力用シートとマスタシートを分けると、検索数式を保ちながら元データの更新を集約できます。
ただし、商品コードが重複している場合は先頭の一致データが返るため、マスタ側ではコードの重複を防ぎましょう。
【操作のポイント】マスタの先頭行は見出しにして、数式の参照範囲には2行目以降のデータだけを指定します。
検索方向と検索モードの指定
続いては、下から検索する方法、右から左へ情報を返す方法、重複データを扱うときの検索モードを確認していきます。
| A列 | B列 | C列 |
|---|---|---|
| 日付 | 商品コード | 販売数 |
| 2026/10/01 | P001 | 3 |
| 2026/10/02 | P001 | 5 |
右から左への検索
VLOOKUP関数は検索列より右側の列しか返せませんが、XLOOKUP関数にはその制限がありません。
たとえばC列の商品コードを探し、A列のカテゴリを返すような右から左への検索にも対応できます。
=XLOOKUP(F2,$C$2:$C$100,$A$2:$A$100,”該当なし”)
XLOOKUP関数では検索範囲と戻り値の範囲を個別に指定するため、列の位置に左右されません。
一覧表の列順を変更する予定があるブックでも、列番号を使わないXLOOKUP関数は管理しやすい選択肢です。
【操作のポイント】検索列がどこにあるかではなく、検索範囲と戻り値の範囲の行数が一致しているかを確認します。
最後の一致データの取得
販売履歴や更新履歴のように同じコードが複数回現れるデータでは、最新の行を取得したいことがあります。
6番目の引数に-1を指定すると、末尾から先頭へ向かって検索できます。
=XLOOKUP(F2,$B$2:$B$100,$C$2:$C$100,”該当なし”,0,-1)
この数式はB列の商品コードを下から検索し、最後に登録された一致行の販売数を返します。
最新データを返すには、履歴表を日付順または入力順に管理することが前提です。
日付そのものを返す場合は、戻り値の範囲をA列に変更します。
【操作のポイント】検索方向を-1にする前に、表の下側が新しいデータになる並びになっているか確認します。
重複データと抽出範囲
XLOOKUP関数は、同じ検索値が複数ある場合、設定した検索方向で最初に見つかった1件を返します。
そのため、複数の注文履歴をすべて抽出する用途には適していません。
複数件を表示したいときは、FILTER関数で条件に合う行をまとめて取り出す方法が向いています。
XLOOKUP関数は一対一の照合、FILTER関数は一対多の抽出という使い分けを意識すると、関数選びが明確になります。
重複が想定されないマスタでは、COUNTIF関数などを使ってコードの重複を事前に確認しておくと安心です。
【操作のポイント】検索結果が想定と違うときは、数式より先に検索キーの重複と表の並び順を確認します。
VLOOKUP関数との違い
続いては、XLOOKUP関数とVLOOKUP関数の違い、利用時の注意点、古いExcelとの互換性を確認していきます。
| 比較項目 | XLOOKUP | VLOOKUP |
|---|---|---|
| 検索方向 | 左右どちらも可能 | 右方向が基本 |
| 戻り値指定 | 範囲を直接指定 | 列番号を指定 |
| 未検出時 | 引数で指定可能 | IFERRORなどが必要 |
列番号指定との違い
VLOOKUP関数では、検索範囲全体を指定した後に、何列目の値を返すかを列番号で指定します。
列を挿入または削除すると列番号と実際のデータの位置が合わなくなり、意図しない値を返すことがあります。
XLOOKUP関数は戻り値の範囲を直接指定するため、数式の意味を確認しやすい構造です。
表の列構成が変わりやすい業務では、戻り値の範囲を明示できるXLOOKUP関数が特に役立ちます。
【操作のポイント】既存のVLOOKUP数式を置き換える際は、検索範囲と戻り値の範囲を別々に確認します。
エラー処理の違い
VLOOKUP関数で見つからない場合の表示を変えるには、IFERROR関数を組み合わせる書き方が一般的です。
XLOOKUP関数では、4番目の引数に表示内容を入力できるため、数式を短く保てます。
=XLOOKUP(F2,$A$2:$A$100,$B$2:$B$100,”登録データなし”)
空白を表示したい場合は、4番目の引数に””を指定します。
ただし、空白では未登録なのか、元データが空白なのかが分かりにくいこともあります。
【操作のポイント】利用者へ確認を促したい帳票では、空白ではなく短いエラーメッセージを設定します。
利用可能なExcelバージョン
XLOOKUP関数は、Microsoft 365やExcel 2021以降など、比較的新しいExcelで利用できます。
古いバージョンのExcelでファイルを開く人がいる場合、XLOOKUP関数が認識されず、数式の互換性に問題が出る可能性があります。
共有先の環境が不明な場合は、利用しているExcelのバージョンを確認してから関数を採用しましょう。
互換性を優先する必要がある場合は、VLOOKUP関数またはINDEX関数とMATCH関数の組み合わせを選ぶ場面もあります。
【操作のポイント】社内共有や取引先への提出前には、受け取る側のExcel環境を確認します。
まとめ エクセルのXLOOKUP関数の検索・抽出
XLOOKUP関数は、検索値に対応する商品名、単価、部署名、在庫数などを抽出するための関数です。
基本形は、検索値、検索範囲、戻り値の範囲の順に指定します。
完全一致のコード検索では、検索範囲と戻り値の範囲を固定し、見つからない場合の表示も設定しておくと実務で使いやすくなります。
複数列を戻り値の範囲に指定すれば、1つの数式から複数項目を横方向へまとめて抽出できます。
右から左への検索や最後に一致したデータの取得もできるため、従来のVLOOKUP関数では扱いにくかった表にも対応可能です。
一方で、重複するデータをすべて抽出したい場合はFILTER関数を検討し、共有先に古いExcel環境がある場合は互換性にも注意しましょう。
検索キーの表記を統一し、マスタデータの重複を防ぎながらXLOOKUP関数を使うことで、入力作業と確認作業を効率化できます。
