ExcelのXLOOKUPで別表から値を取り出す｜見つからない場合も解説
商品コードから単価を探す例で、検索値・探す列・返す列を図解。未登録や同じコードが複数ある場合の注意点も確認します。
対象：Excel for Microsoft 365／Excel 2024・2021を対象。Excel 2016・2019ではXLOOKUPを使えません。
公式資料の確認日：2026-11-10
E2の商品コードをA列から探し、同じ行のC列を返します。P102なら80円。見つからない場合の表示も、式の中で指定できます。
この記事の道のり
1．最初に、使えるExcelのバージョンを確認する
XLOOKUPを入力しても関数として認識されない場合は、まず利用環境を確認します。この記事の対象はMicrosoft 365、Excel 2024、Excel 2021です。Excel 2016・2019ではXLOOKUPを利用できません。
共同作業でファイルを渡す場合は、受け取る人のバージョンも確認します。古いExcelが混ざるなら、VLOOKUPなど別の方法を選ぶことも検討します。
仕様の確認：Microsoft：XLOOKUP 関数 ↗
2．小さな商品マスターを用意する
下の表を空のワークシートのA1に貼り付けます。商品コードはA2:A4、商品名はB2:B4、単価はC2:C4です。コードと金額はこの記事の架空の練習データです。
続いてE1に「検索コード」、E2にP102、F1に「単価」と入力します。F2へ数式を入れるので、元の商品マスターには式を書き込みません。
練習データ：A1セルへ貼り付け
| 商品コード | 商品名 | 単価 |
|---|---|---|
| P101 | ノート | 120 |
| P102 | ボールペン | 80 |
| P103 | 付箋 | 150 |
商品コード 商品名 単価 P101 ノート 120 P102 ボールペン 80 P103 付箋 150
ボタンでコピーできない場合は、枠内の文字を選択してコピーできます。列が分かれない場合は、タブ区切りのテキストとして取り込んでください。
3．検索値・探す列・返す列を順に指定する
F2に次の式を入れます。最初はE2の検索値、次がコードを探す範囲、次が単価を返す範囲です。4つ目の「未登録」は一致が見つからない場合の表示。最後の0は完全一致を指定しています。
=XLOOKUP(E2,$A$2:$A$4,$C$2:$C$4,"未登録",0)
期待する結果：E2がP102なら80円
A3にあるP102が見つかり、同じ3行目のC3にある80が返ります。$付きの範囲は、式をコピーしても参照がずれないようにしています。検索するコードだけはE2から読みます。
検索範囲と戻り範囲は、対応する行が同じ順番・同じ長さになっている必要があります。片方の列だけを並べ替えると、コードと商品の対応自体が壊れてしまいます。
仕様の確認：Microsoft：XLOOKUP 関数 ↗
4．商品名も取り出してみる
G1に「商品名」、G2に次の式を入れると、戻り範囲をB列へ変えた例になります。E2は同じまま使います。
=XLOOKUP(E2,$A$2:$A$4,$B$2:$B$4,"未登録",0)
期待する結果：E2がP102ならボールペン
E2をP101に変えると、F2は120、G2はノートになります。P103なら150と付箋です。コードを1つ変えて複数の結果が対応して変わるか確かめましょう。
実際の業務では、表示された単価を使って数量×単価を計算することもあります。見つからないときに文字を返す設計なら、その後の計算でエラーにならないよう、登録の有無も分けて確認します。
5．存在しないコードは「未登録」と表示する
E2をP999に変えます。商品表にはそのコードがないので、F2・G2は「未登録」になります。0円と表示してしまうと、本当に単価が0円の商品と区別できません。見つからない状態には別の表示を用意します。
「未登録」は検索が一致しなかった場合の表示で、どんなエラーも隠す指定ではありません。範囲のサイズ違いなど別の原因は、そのまま直す必要があります。大量の式をまとめてIFERRORで隠す前に、まず原因を確認してください。
仕様の確認：Microsoft：XLOOKUP 関数 ↗
6．同じコードが2件あったら、最初の一致が返る
通常の検索では上から最初に見つかった一致を返します。仮にP102の行をもう1つ作り、単価を999にしても、最初のP102が80なら通常の式は80を返します。XLOOKUPは重複データがないことまで保証してくれません。
単価マスターのコードを一意にする必要があるなら、先にCOUNTIFなどで出現回数を確認します。商品コードが同じでも期間別の価格を持つ設計なら、コードだけでは検索条件が足りない可能性があります。
仕様の確認：Microsoft：XLOOKUP 関数 ↗
7．#NAME?・#N/A・#VALUE!が出たら確認すること
#NAME?は、バージョン非対応や関数名などを確認します。#N/Aは、検索値が存在するか、前後に余計な空白がないかを調べます。数字の123と文字列の「123」を同じものとして扱ってよいかも、データ側で確認してください。
#VALUE!などが出たときは、検索範囲と戻り範囲の長さや向きを見直します。A2:A4を探すのにC2:C5を返す、といった指定は避けます。数式が正しくても、元の表でコードと商品がずれていないかまで確認しましょう。
8．3つの正常例と1つの未登録例で検算する
P101→120円・ノート、P102→80円・ボールペン、P103→150円・付箋、P999→未登録。4つの入力で結果を確認します。先頭の1件だけでなく、途中・最後・見つからない場合を試すのがポイントです。
別シートに商品マスターを置く場合は、検索範囲と戻り範囲へシート名を付けます。最初はこの1シートの小さな例で式の役割を理解してから、データ量や参照先を増やしましょう。
参考資料と確認範囲
公式資料の仕様と、練習データの期待結果を照合しています。図は当サイトが作成した模式図で、Excel実機の画面撮影・操作検証は行っていません。メニューの位置や表記は利用環境で確認してください。