ExcelのXLOOKUP関数の使い方を初心者向けに解説｜VLOOKUPとの違い・複数条件・エラー対策

Excelで商品コードや社員番号を入力したときに、対応する商品名・単価・部署名を自動表示させたいものの、「どの関数を使えばよいのか分からない」「VLOOKUPは列番号の指定が難しい」と悩む方は少なくありません。XLOOKUP関数を使えば、検索する列と表示する列を分けて指定でき、表の左側にある値も取得できます。基本の使い方からVLOOKUPとの違い、複数条件、最新データの検索、エラー対策まで、実際の業務表を想定しながら初心者向けに解説します。

XLOOKUP関数とは

XLOOKUP関数は、指定した値を表やセル範囲から探し、同じ行または列にある別の値を返すExcel関数です。商品コードから商品名を表示する、社員番号から所属部署を取得する、取引先コードから支払条件を確認するといった業務に使えます。

Microsoftの説明では、XLOOKUPは検索範囲と戻り配列を個別に指定でき、戻り値が検索列の左右どちらにあっても取得できます。初期設定が完全一致である点も、初心者が扱いやすい特徴です。

XLOOKUP関数の基本構文

XLOOKUP関数の構文は次のとおりです。

=XLOOKUP(検索値,検索範囲,戻り配列,[見つからない場合],[一致モード],[検索モード])

引数	役割	入力例
検索値	探したい商品コードや社員番号	F2
検索範囲	検索値を探すセル範囲	A2:A100
戻り配列	見つかった行から返すセル範囲	B2:B100
見つからない場合	該当データがないときに表示する内容	該当なし
一致モード	完全一致・近似一致・ワイルドカードを指定	0
検索モード	上から検索するか、下から検索するかを指定	-1

最初に覚える段階では、検索値・検索範囲・戻り配列の3つを理解すれば十分です。完全一致は既定の設定なので、通常の商品コードや社員番号の照合では一致モードを省略できます。

最も基本的な数式

A列に商品コード、B列に商品名があり、F2に入力した商品コードに対応する商品名を表示する場合は、次の数式を使用します。

=XLOOKUP(F2,A2:A100,B2:B100)

ExcelはF2の値をA2:A100から探し、見つかった行と同じ位置にあるB列の商品名を返します。たとえばF2に「P005」と入力し、A10に「P005」がある場合は、B10の値が表示されます。

XLOOKUPを使えるExcelのバージョン

XLOOKUPはMicrosoft 365、Excel 2021、Excel 2024などで利用できます。一方、Excel 2016とExcel 2019では使用できません。古いExcelを使用している取引先や社内メンバーとファイルを共有する場合は、事前に利用環境を確認してください。

XLOOKUPとVLOOKUPの違い

XLOOKUPとVLOOKUPは、どちらも表からデータを検索する関数です。ただし、検索方向、数式の作り方、完全一致の指定方法などに違いがあります。

VLOOKUPでは検索値が指定範囲の最初の列にある必要があり、取得する列を左端から数えた列番号で指定します。また、検索方法を省略すると近似一致として処理されます。

比較項目	XLOOKUP	VLOOKUP
検索できる方向	右方向・左方向の両方	基本的に右方向
戻り値の指定	戻り配列を直接指定	列番号を指定
既定の一致方法	完全一致	検索方法を省略すると近似一致
見つからない場合	関数内で表示内容を指定可能	IFERRORなどとの組み合わせが必要
下からの検索	検索モードで指定可能	別の関数や工夫が必要
列追加への強さ	戻り範囲を直接指定するため影響を受けにくい	列番号がずれる可能性がある
古いExcelへの対応	一部の旧バージョンでは使用不可	幅広いバージョンで使用可能

左側の列から値を取得できる

A列に社員名、B列に社員番号があり、社員番号から社員名を取得するケースを考えます。検索列である社員番号より、戻したい社員名の列が左側にあります。

XLOOKUPなら、次の数式で取得できます。

=XLOOKUP(F2,B2:B100,A2:A100,”該当なし”)

VLOOKUPは指定範囲の左端列を検索する仕組みなので、このような左方向への検索には向いていません。XLOOKUPでは検索範囲と戻り配列が独立しているため、列の並び順を変更せずに対応できます。

列を追加しても数式が壊れにくい

VLOOKUPでは「範囲の左から何列目を返すか」を数字で指定します。表の途中に列を挿入すると、意図しない列の値を返すことがあります。

XLOOKUPは戻り配列を「C2:C100」のように直接指定します。新しい列を追加しても参照先が自動調整されやすく、担当者が頻繁に項目を追加する管理表にも適しています。

完全一致を指定し忘れにくい

VLOOKUPでは、完全一致にするために第4引数へFALSEまたは0を入力します。省略すると近似一致になるため、商品コードや社員番号の検索で誤った値が表示される原因になります。

XLOOKUPは完全一致が既定です。基本形の3つの引数だけでも、通常のコード検索を安全に始められます。

XLOOKUP関数の基本的な使い方

ここからは、商品マスタを使ってXLOOKUPの数式を作成します。A列に商品コード、B列に商品名、C列に単価、D列に在庫数が入力されている想定です。

商品コードから商品名を表示する手順

1. 商品マスタのA2:A100に商品コードを入力します。
2. B2:B100に商品名を入力します。
3. 検索用セルF2に調べたい商品コードを入力します。
4. 商品名を表示するG2を選択します。
5. G2にXLOOKUPの数式を入力します。
6. Enterキーを押し、正しい商品名が表示されるか確認します。

=XLOOKUP(F2,$A$2:$A$100,$B$2:$B$100,”該当なし”)

検索範囲と戻り配列にドル記号を付けて絶対参照にしておくと、数式を下方向へコピーしても参照範囲がずれません。

商品コードから単価を表示する

商品名ではなく単価を表示したい場合は、戻り配列をC列へ変更します。

=XLOOKUP(F2,$A$2:$A$100,$C$2:$C$100,”該当なし”)

検索値と検索範囲は同じまま、戻り配列だけを変更する点がポイントです。XLOOKUPでは「どこから探して、どこから返すか」を数式から読み取りやすくなっています。

商品名・単価・在庫数をまとめて表示する

戻り配列に複数列を指定すると、商品名・単価・在庫数を横方向へまとめて表示できます。

=XLOOKUP(F2,$A$2:$A$100,$B$2:$D$100,”該当なし”)

結果を表示する右側のセルに文字や数式が入っていると、展開できずにエラーになります。複数列を返すときは、結果が広がる範囲を空けておきましょう。

Excelテーブルを使うと範囲管理が楽になる

商品マスタをExcelテーブルに変換すると、商品が追加されたときに参照範囲が自動で広がります。テーブル名を「商品マスタ」とした場合は、次のような数式にできます。

=XLOOKUP(F2,商品マスタ[商品コード],商品マスタ[商品名],”該当なし”)

セル番地だけの数式よりも、「商品コードから商品名を返している」と理解しやすくなります。複数人で管理する業務ファイルでは、テーブルと列名を使った数式が引き継ぎにも役立ちます。

XLOOKUPで見つからない場合の表示を設定する

検索値が表に存在しないと、通常は「#N/A」が表示されます。業務で利用する入力フォームや見積書では、利用者がエラーだと判断しにくいため、第4引数で分かりやすい表示を設定します。

「該当なし」と表示する

=XLOOKUP(F2,$A$2:$A$100,$B$2:$B$100,”該当なし”)

登録されていない商品コードが入力された場合に「該当なし」と表示されます。

何も表示しない

=XLOOKUP(F2,$A$2:$A$100,$B$2:$B$100,””)

入力前の帳票をすっきり見せたい場合は空文字を指定できます。ただし、空白のままだと「未入力なのか、商品が存在しないのか」が分かりません。入力担当者が確認する管理表では「未登録」や「商品コードを確認」と表示したほうが原因を判断しやすくなります。

エラーを隠しすぎない

見た目を整えるためにすべてのエラーを空白にすると、商品マスタの登録漏れに気づけない場合があります。請求書や見積書の印刷欄では空白、管理者用の確認列では「未登録」と表示するなど、利用目的によって使い分ける方法が実務的です。

XLOOKUPで複数条件を指定する方法

実務では「社員番号だけ」ではなく、「社員番号と対象月」「商品コードと店舗名」のように、複数条件が一致したデータを探したい場面があります。

社員番号と対象月の2条件で検索する

A列に社員番号、B列に対象月、D列に支給額があるとします。H2に社員番号、H3に対象月を入力し、該当する支給額を取得する数式は次のとおりです。

=XLOOKUP(1,($A$2:$A$100=H2)*($B$2:$B$100=H3),$D$2:$D$100,”該当なし”)

「A列がH2と同じか」「B列がH3と同じか」をそれぞれ判定し、両方が一致した行だけが1になります。XLOOKUPは、その1を検索してD列の値を返します。

複数条件でつまずきやすいポイント

• 検索条件を入力したセルのデータ形式が元データと一致しているか確認する
• 日付が文字列ではなくExcelの日付として保存されているか確認する
• 検索範囲と戻り配列の開始行・終了行をそろえる
• 同じ条件のデータが複数ある場合、先頭の1件だけが返ることを理解する
• 広すぎる列全体参照を多用せず、必要な範囲やテーブルを指定する

同じ社員番号と対象月のデータが複数登録される可能性がある場合は、検索関数よりもデータの重複を防ぐ入力ルールを整えることが先です。XLOOKUPで結果が表示されても、元データに重複があれば正しい業務結果とは限りません。

XLOOKUPの応用的な使い方

部分一致で検索する

商品名の一部を入力して検索したい場合は、一致モードに2を指定し、ワイルドカードを使用します。

=XLOOKUP(“*”&H2&”*”,$B$2:$B$100,$A$2:$A$100,”該当なし”,2)

H2に「プリンター」と入力すると、商品名に「プリンター」を含む最初のデータの商品コードを返します。アスタリスクは0文字以上の任意の文字を表します。

同じキーワードを含む商品が複数ある場合は、最初の1件だけが表示されます。候補をすべて一覧表示したい業務にはFILTER関数のほうが適しています。

最後に登録されたデータを取得する

同じ取引先コードの履歴から、最も下にある最新の取引金額を取得する場合は、検索モードに-1を指定します。

=XLOOKUP(H2,$A$2:$A$100,$D$2:$D$100,”該当なし”,0,-1)

一致モードの0は完全一致、検索モードの-1は末尾から先頭へ検索する指定です。XLOOKUPでは先頭検索の1と逆方向検索の-1を選択できます。

ただし、「表の一番下にあるから最新」とは限りません。並び替えが崩れる可能性がある表では、日付列を基準に最新データを判定する仕組みも検討してください。

金額帯や評価基準を近似一致で判定する

A列に購入金額の基準値、B列に会員ランクが昇順で入力されている場合、入力金額以下で最も近い基準値を探せます。

=XLOOKUP(H2,$A$2:$A$6,$B$2:$B$6,””,-1)

一致モードの-1は、完全一致がない場合に次に小さい値を返します。送料区分、手数料率、評価ランクなどの段階表に利用できます。

近似一致は、基準値の並びや境界条件を誤ると結果が変わります。実際の金額を数件入力し、境界の直前・境界と同額・境界の直後で結果を確認してください。

縦と横の2方向から検索する

行に商品名、列に月が並ぶ売上表から、指定した商品と月が交差する金額を取得する場合は、XLOOKUPを入れ子にします。

=XLOOKUP(H2,$A$3:$A$10,XLOOKUP(H3,$B$2:$E$2,$B$3:$E$10),”該当なし”)

内側のXLOOKUPで対象月の列を取得し、外側のXLOOKUPで対象商品の行を探します。Microsoftの公式例でも、入れ子にしたXLOOKUPによる縦方向と横方向の検索が紹介されています。

XLOOKUPで発生しやすいエラーと対処法

XLOOKUPの数式が動かないときは、数式だけでなく元データの形式や入力ルールも確認します。見た目が同じ値でも、数値と文字列では一致しないことがあります。

症状・エラー	主な原因	対処法
#N/A	検索値が存在しない、文字列と数値が混在している	元データと検索値を確認し、第4引数も設定する
#VALUE!	検索範囲と戻り配列の行数が異なる	開始行と終了行をそろえる
#NAME?	関数名の入力ミス、利用中のExcelが非対応	スペルとExcelのバージョンを確認する
#REF!	参照している列やシートが削除された	参照範囲を設定し直す
#SPILL!	複数列の結果を展開するセルに値が入っている	結果が表示される範囲を空ける
別の値が表示される	重複データがある、近似一致の指定が誤っている	重複と一致モードを確認する
空欄なのに0が表示される	戻り先のセルが空白	IF関数などで空白表示を制御する

#N/Aが表示される場合

最初に検索値が本当に検索範囲内にあるか確認します。特に商品コードでは、次の違いが原因になりやすいです。

• 半角と全角が混在している
• 数字が数値ではなく文字列として保存されている
• 先頭や末尾に不要な空白が入っている
• ハイフンの種類が異なる
• 先頭の0が削除されている

商品コードや社員番号は、列ごとにデータ形式を統一してください。入力規則のリストやマスタからの選択方式にすると、手入力による表記揺れを減らせます。

#VALUE!が表示される場合

検索範囲がA2:A100なのに、戻り配列がB2:B90になっているなど、範囲の大きさが異なると正しく処理できません。

=XLOOKUP(F2,A2:A100,B2:B90)

上記のような数式ではなく、次のように開始行と終了行をそろえます。

=XLOOKUP(F2,A2:A100,B2:B100)

#NAME?が表示される場合

「XLOKUP」のような入力ミスがないか確認します。スペルが正しい場合は、利用中のExcelがXLOOKUPに対応していない可能性があります。特にExcel 2016やExcel 2019を使用している場合は、VLOOKUPやINDEX関数とMATCH関数の組み合わせを検討してください。

正しい数式なのに結果が違う場合

同じ検索値が複数存在すると、通常は最初に見つかったデータが返ります。「数式は動いているが、表示内容が古い」という場合は、元データに重複がないか確認してください。

取引履歴のように重複が正常な表では、検索モード-1を使うか、日付も含めた複数条件にする必要があります。

エラー確認の手順

1. 検索値が元データに存在するか確認します。
2. 検索値と元データの表示形式を確認します。
3. 検索範囲と戻り配列の行数を確認します。
4. 絶対参照がずれていないか確認します。
5. 重複データの有無を確認します。
6. 一致モードと検索モードを確認します。
7. 利用しているExcelのバージョンを確認します。

XLOOKUPを実務で使う場面

見積書や請求書の商品情報を自動表示する

商品コードを選択すると、商品名・単価・税区分を商品マスタから取得できます。毎回商品名や単価を入力する必要がなくなり、入力ミスや古い単価の使用を減らせます。

ただし、過去の請求書まで常に最新単価へ更新される設計には注意が必要です。確定済みの帳票は値として保存する、発行時点の価格履歴を残すなど、証跡を維持できる運用を検討してください。

在庫管理表で商品情報を補完する

入出庫履歴に商品コードを入力し、商品名・カテゴリー・保管場所を商品マスタから取得します。担当者が商品名を手入力するよりも表記が統一され、集計しやすくなります。

社員名簿から部署や連絡先を取得する

社員番号を基準に、氏名・部署・役職・メールアドレスを表示できます。申請書や研修管理表など、複数の業務表で社員情報を再入力する手間を減らせます。

個人情報を含むファイルでは、閲覧権限や共有範囲も確認してください。関数で取得できることと、業務上閲覧してよいことは別の問題です。

取引履歴から最新情報を取得する

検索モード-1を利用すると、取引先ごとの最新購入日や最終対応内容を取得できます。顧客管理表や問い合わせ履歴の簡易的な確認に役立ちます。

履歴件数が多い場合や、複数人が同時に編集する場合は、Excelだけで管理を続けるより、データベースや業務アプリへの移行が適していることもあります。

XLOOKUPを使う前に整えたいデータ設計

XLOOKUPを使えば、整理されていない表が自動的に正しくなるわけではありません。検索関数を安定して動かすには、元データの作り方が大切です。

名称ではなく一意のIDで検索する

会社名や商品名は、同名データや表記揺れが起こりやすい項目です。検索値には取引先コード、商品コード、社員番号など、1件を特定できるIDを使います。

1行に1件のデータを入力する

結合セルや複数行にまたがる見出しがあると、検索範囲を管理しにくくなります。1行に1商品、1行に1取引、1行に1社員という形にそろえます。

重複を許可する列と許可しない列を分ける

商品マスタの商品コードは重複させず、取引履歴の商品コードは重複してもよいというように、列の役割を決めます。重複を許可しないマスタでは、条件付き書式やデータの入力規則も併用すると確認しやすくなります。

数式だけでなく確認列を用意する

「商品名が表示されたから正しい」と判断するのではなく、未登録コードや重複コードを検出する確認列を作ると、担当者が問題に気づきやすくなります。

XLOOKUPが向いている人・向いていない人

XLOOKUPが向いているケース

• Microsoft 365や比較的新しいExcelを使用している
• 商品マスタや社員マスタから情報を取得したい
• VLOOKUPの列番号指定でミスが起こりやすい
• 検索列より左側のデータを取得したい
• 最新履歴を下から検索したい
• 表の列追加や変更が多い

XLOOKUPが向いていないケース

• Excel 2016やExcel 2019の利用者とファイルを共有する
• 一致するすべてのデータを一覧表示したい
• 条件に合う数値を合計したい
• 大量の履歴データを複数人で同時管理したい
• 元データの重複や表記揺れが整理されていない

複数件を抽出するならFILTER関数、条件別に合計するならSUMIFS関数、集計結果を分析するならピボットテーブルが適しています。すべてをXLOOKUPだけで解決しようとせず、目的に合う機能を選びましょう。

AIに任せてよい部分と人が確認する部分

生成AIは、表の列構成と実現したい結果を伝えると、XLOOKUPの数式案やエラー原因の候補を作成できます。複数条件や入れ子の数式を一から組み立てる際の補助として役立ちます。

AIに任せやすい作業

• 目的に合うXLOOKUP数式の下書き
• 数式の各引数の説明
• VLOOKUPからXLOOKUPへの書き換え案
• エラー原因の候補整理
• テストデータや確認項目の作成

人が確認すべき作業

• 検索範囲と戻り配列が正しいか
• 元データに重複や表記揺れがないか
• 計算結果が業務ルールと一致しているか
• 境界値や未登録データで正しく動くか
• 機密情報をAIへ入力してよいか

AIが作成した数式は、そのまま本番ファイルへ貼り付けず、コピーしたファイルや少量のテストデータで確認します。数式がエラーにならないことだけでなく、返された値が業務上正しいかを人が判断する必要があります。

よくある質問

XLOOKUPは検索列より左側の値も取得できますか？

取得できます。検索範囲と戻り配列を別々に指定するため、戻したい列が検索列の左側にあっても問題ありません。

XLOOKUPで完全一致を指定する必要はありますか？

通常は必要ありません。XLOOKUPは完全一致が既定です。近似一致やワイルドカード検索を行うときだけ、一致モードを指定します。

XLOOKUPで複数の検索結果をすべて表示できますか？

XLOOKUPが返すのは基本的に最初または最後に一致した1件です。条件に一致する複数行を一覧表示したい場合は、FILTER関数を検討してください。

XLOOKUPで複数条件を指定できますか？

指定できます。「条件1の判定結果×条件2の判定結果」を検索範囲として利用します。ただし、同じ条件のデータが複数あると、最初の1件だけが返ります。

XLOOKUPで#NAME?が表示されるのはなぜですか？

関数名の入力ミス、または利用しているExcelがXLOOKUPに対応していない可能性があります。Excel 2016やExcel 2019では利用できないため、バージョンを確認してください。

VLOOKUPの数式はすべてXLOOKUPへ変更したほうがよいですか？

正常に動作している数式を急いで変更する必要はありません。新しく作るファイルや、列追加によるミスが多いファイルから段階的に切り替える方法が安全です。

空白セルを検索すると0が表示される場合はどうすればよいですか？

戻り先が空白の場合、0として表示されることがあります。IF関数と組み合わせて、取得結果が空白なら空文字を表示するように調整します。

XLOOKUPの数式を下へコピーすると結果がずれるのはなぜですか？

検索範囲や戻り配列が相対参照になっている可能性があります。F4キーなどで「$A$2:$A$100」のような絶対参照に固定するか、Excelテーブルを使用してください。

おすすめAIツール

ChatGPTでXLOOKUPの数式案を作成する

ChatGPTには、「F2の商品コードをA列から探し、C列の単価を表示したい」「A列の社員番号とB列の対象月が一致する支給額を取得したい」のように、目的を自然な日本語で伝えられます。

ChatGPT for Excelでは、スプレッドシートの作成・更新・説明や、壊れた数式の確認などをExcel内から支援できます。ただし、利用できる機能や上限はプランや管理者設定によって異なり、生成された数式が不完全な場合もあるため、保存や共有の前に確認が必要です。

社外秘の売上データ、顧客情報、社員情報を含むファイルを扱う場合は、会社のAI利用ルールとデータ取り扱い方針を確認してください。数式だけを相談するときは、実際の名称や数値を架空の内容へ置き換える方法もあります。

関連記事

• VLOOKUPとXLOOKUPの違いとは？どちらを覚えるべきか初心者向けに解説
• ChatGPTでXLOOKUPを作る方法｜Excel関数をAIで簡単作成する手順
• ChatGPTでVLOOKUPを作る方法｜Excel関数をAIで簡単作成する手順と活用例
• ChatGPTでExcel関数を作る方法｜初心者でも簡単に関数作成を効率化

まとめ

• XLOOKUPは検索範囲と戻り配列を直接指定でき、左右どちらの列からでも値を取得できる
• 基本形は検索値・検索範囲・戻り配列の3つで、完全一致が既定になっている
• 複数条件、部分一致、逆方向検索、縦横2方向の検索にも対応できる
• エラーが出たときは、数式だけでなくデータ形式・範囲・重複・Excelのバージョンも確認する

XLOOKUPを使い始めるときは、まず小さな商品マスタを作り、商品コードから商品名を表示する基本形を試してみてください。基本の3引数を理解してから、見つからない場合の表示、複数条件、最新データの検索へ進むと、数式の意味を見失わずに実務へ取り入れられます。