XLOOKUP関数の使い方を完全解説｜0を表示しない・複数条件・別シート・合計まで対応

本記事には広告（PR）が含まれます。
くわしくは プライバシーポリシー をご確認ください。

Excel関数のなかでも、従来から使われてきた「VLOOKUP関数」は多くの方にとっておなじみですが、
Excel 365やExcel 2021以降では、その進化版ともいえる「XLOOKUP関数」が利用できるようになりました。

XLOOKUPは、VLOOKUPの弱点を解消し、より柔軟でミスの起きにくい検索ができる新しい関数です。
ただ、初めて使うと「値が見つからない」「0が表示される」「複数条件で使いたいのにうまくいかない」など、
細かいつまずきも多く、“結局VLOOKUPでいいや…”と諦めてしまうケースも少なくありません。

この記事では、基本構文や対応バージョンに加えて、0やエラーが表示される原因、複数条件・別シート・重複データへの対応までまとめています。詳しい内容は、以下の一覧から確認できます。

XLOOKUP関数とは？できることをざっくり解説

XLOOKUPは、表から値を検索して結果を返す関数です。VLOOKUPより柔軟で、左右どちら方向でも検索できます。

基本構文は以下の通りです：

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

=XLOOKUP(80, A2:A5, B2:B5, , -1)

=XLOOKUP(F2,A2:A7,C2:C7,"未登録",0,-1)

VLOOKUPとの違い（列方向、柔軟性、複数一致など）

XLOOKUP関数は、VLOOKUPの不便な点を解消した機能が多数搭載されています。
代表的な違いを以下にまとめます。

VLOOKUPでは「左に戻れない」「列挿入で壊れる」「複数条件に対応しにくい」といった制限がありましたが、
XLOOKUPではこれらの弱点が大きく改善されているのが特徴です。

XLOOKUPはいつから使える？対応バージョンと確認方法

XLOOKUP関数は、すべてのExcelで使えるわけではありません。
「入力したのに候補が出ない」「#NAME?になる」という場合は、バージョン非対応の可能性があります。

XLOOKUPが使える主な環境は次のとおりです。

• Microsoft 365（旧Office 365）：利用可

• Excel 2021（永続ライセンス）以降：利用可

• Excel for the web（Web版）：基本は利用可（機能差が出る場合あり）

• Excel 2019以前：基本的に利用不可

いま使っているExcelで確認する方法（いちばん簡単）

セルに「=XLOOKUP(」と入力してみて、候補にXLOOKUPが出てくれば使えます。
候補が出ない・#NAME? になる場合は、そのExcelでは利用できません。

使えない場合の代わりは？

Excel 2019以前でも、**INDEX＋MATCH（またはVLOOKUP）**で同じような検索は可能です。
ただし、XLOOKUPのように「左方向検索」や「見つからない時の表示」までスマートに書けないため、可能なら対応版への移行がおすすめです。

XLOOKUPの便利な使い方とエラー対策

よくあるエラーと原因【#N/A・#VALUE!・スピルエラー】

XLOOKUP関数を使っていて最も多いのが「正しく設定しているのにエラーが出る」という悩みです。
特に以下のエラーがよく発生します。

それぞれの対処法については簡易的に以下のような対応ができます。

• #N/A 対策：IFNA関数 や IFERROR関数 でエラーを制御

• #VALUE! 対策：検索範囲と戻り値範囲のサイズを一致させる

• #SPILL! 対策：空白セルを確保する／結合セルを解除する

「0を表示しない」「空白にしたい」場合の設定

XLOOKUPで「0」が表示される場合は、まず原因を確認しましょう。

主に次のようなケースがあります。

• 検索値は見つかったが、戻り値に指定したセルが空白だった
• 戻り値のセルに実際の数値「0」が入力されている
• 検索値が見つからず、エラー処理として0を指定している

検索値が見つからないときに空白を表示する

検索値が見つからない場合は、XLOOKUPの第4引数に空文字列""を指定します。

=XLOOKUP(検索値,検索範囲,戻り値範囲,"")

これにより、一致するデータがない場合の#N/Aを空白表示にできます。

結果が0のときに空白を表示する

検索結果が0だった場合に空白を表示するには、IF関数を組み合わせます。

=IF(XLOOKUP(検索値, 検索範囲, 戻り値範囲)="", "", XLOOKUP(検索値, 検索範囲, 戻り値範囲,""))

=LET(
x,XLOOKUP(検索値,検索範囲,戻り値範囲,""),
IF(x=0,"",x)
)

LET関数は計算結果に名前を付け、同じ処理を繰り返さずに数式内で再利用できる関数です。XLOOKUPとLETはExcel 2021でも利用できます。

参照先の空白を文字列として返す方法

戻り値を文字列として扱ってよい場合は、末尾に&""を付ける方法もあります。

=XLOOKUP(検索値,検索範囲,戻り値範囲,"")&""

ただし、数値も文字列に変換されるため、その後に合計や計算を行う列には向きません。

XLOOKUP以外のエラーもまとめて処理する方法

XLOOKUP関数の検索値が見つからない以外のエラーにも対応したい場合は、IFERROR関数を利用する方法もあります。

=IFERROR(XLOOKUP(検索値, 検索範囲, 戻り値範囲), "")

=IFERROR(XLOOKUP(...), "エラー発生")

重複データや複数条件での使い方

XLOOKUP関数は、検索範囲内に同じ値が複数ある場合、通常は最初に一致したデータを返します。
そのため「重複データをすべて取得したい」場合や、「複数条件で絞り込みたい」場合は、工夫が必要です。

■ 重複データの取得には？

• Excel 365なら FILTER関数 との併用がおすすめ

• INDEX＋MATCH＋SMALLで順番に取得する方法もあります

■ 複数条件の検索には？

複数条件で検索する場合の基本的な考え方は、次のとおりです。

=XLOOKUP(
検索条件1&検索条件2,
検索列1&検索列2,
戻り値範囲,
"該当なし"
)

※「&」で複数の条件を結合し、条件の組み合わせが一致する行を検索します。値の組み合わせが紛らわしい場合は、条件の間に「|」などの区切り文字を入れると安全です。

XLOOKUPが「重い」と感じるときの対策

XLOOKUPは便利ですが、大量データで何百・何千件と参照すると処理速度が落ちることがあります。
原因と対策は以下の通りです。

他関数との比較（INDEX/MATCH、FILTER）

実務では「一つだけ取得＝XLOOKUP」「複数件抽出＝FILTER」「複雑条件＝INDEX＋MATCH」など、目的に応じた使い分けが効果的です。

目的に合った関数を使い分けることで、作業効率も大きく変わってきます。ぜひ今回の内容を日々の業務に活かしてみてください。

XLOOKUPで別シートを参照する方法（最短でできる）

XLOOKUPは、同じシート内だけでなく別シートの表も普通に参照できます。
実務で多い「マスタは別シート、入力は作業シート」という形でも問題ありません。

例：別シートの表から単価を引く

• 作業シート：商品コードがA2

• Sheet2：A列＝商品コード、B列＝単価

=XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B, "未登録")

ポイントは、検索範囲と戻り値範囲に Sheet名!範囲 を付けるだけです。

シート名にスペースがある場合

シート名にスペースや記号が入っている場合は、シングルクォートで囲みます。

=XLOOKUP(A2, '商品 マスタ'!A:A, '商品 マスタ'!B:B, "未登録")

別ブック（他のExcelファイル）を参照する場合

別ブック参照も可能ですが、ファイルが閉じていると動作が重くなったり、リンク管理が必要になったりします。
実務では、まずは「同じブック内の別シート参照」で運用するのがおすすめです。

XLOOKUPはテーブル（構造化参照）でも使える

検索対象の表をテーブル化している場合、XLOOKUPは列名で指定できるため、範囲がズレにくく管理が楽になります。

例：テーブル「商品表」から単価を返す

（商品表の列：商品コード、単価）

=XLOOKUP(A2, 商品表[商品コード], 商品表[単価], "未登録")

テーブルで使うメリット

• 列を追加・移動しても参照が崩れにくい

• 「どの列を見ているか」が式から読み取りやすい

• 範囲が自動で伸びるので、行追加に強い

「マスタ表はテーブル化＋XLOOKUP」は、初心者でも事故が少ない鉄板の組み合わせです。

XLOOKUPで合計する方法（SUM / SUMIFとの使い分け）

「XLOOKUPで引いた値を合計したい」というケースもよくあります。
ただし、合計の目的によってXLOOKUPが向く場合／SUMIFが向く場合が分かれます。

パターン1：XLOOKUPで取得した結果を合計する（単純な合計）

たとえば、複数のコードから単価を引いて合計したい場合は、XLOOKUP＋SUMでまとめられます。

=SUM(XLOOKUP(A2:A6, 商品表[商品コード], 商品表[単価], 0))

※Excel 365/2021以降で、配列として扱える環境ならこの形が便利です。

パターン2：「条件に合うデータの合計」はSUMIFが向いている

一方で、「商品コードがAの売上合計」「支店が東京の合計」など、条件でまとめて集計する場合はSUMIF / SUMIFSのほうが自然です。

XLOOKUPは「1件を取得する検索関数」、SUMIF / SUMIFSは「条件に合う複数データを集計する関数」という役割の違いがあります。

• 1件だけ引く → XLOOKUP
• 条件に合うものをまとめて集計 → SUMIF / SUMIFS

目的に合わせて使い分けると、式がシンプルになり処理も軽くなります。

▼SUMIF関数の利用例(一つの条件に合ものを集計)：

▼SUMIFS関数の利用例(複数条件に対応)：

重複データの「2番目以降」を取得したいとき（考え方）

XLOOKUPは基本的に、最初に一致した1件だけを返します。
そのため「同じ商品が複数ある」「履歴の2件目を取りたい」という場合は、目的に合わせて方法を選ぶのがポイントです。

1）最後に一致した値が欲しい（最新を優先したい）

重複がある中で「一番下（最後）の値」を返したいなら、検索モードに -1（下から検索） を使います。

=XLOOKUP(F2, A2:A100, C2:C100, "未登録", 0, -1)

これで「同じ値が複数ある場合、最後に出てくるもの」を返せます。

2）重複をすべて一覧で取りたい

複数件を全部出したい場合は、Excel 365なら FILTER関数 が得意です。

GoogleスプレッドシートでXLOOKUPは使える？（Excelとの違い）

Googleスプレッドシートにも XLOOKUP関数は用意されています。
そのため、Excelからスプレッドシートに移行した場合でも、基本の書き方は大きくは変わりません。

ただし、Excelとまったく同じ挙動とは限らず、

• 配列の扱い

• エラー表示の挙動

• 関数の更新状況

などで差が出る場合があります。
「同じ式なのに結果が違う」と感じたときは、まずは検索範囲・データ形式（文字列/数値）・空白の有無をチェックすると解決しやすいです。

よくある質問（FAQ）

XLOOKUPはExcel2019でも使えますか？

VLOOKUPより必ずXLOOKUPの方が良いですか？

XLOOKUPで左方向検索できますか？

=XLOOKUP(E2,C2:C100,A2:A100,"未登録")

XLOOKUPで複数条件検索はできますか？

=XLOOKUP(
検索条件1&検索条件2,
検索列1&検索列2,
戻り値範囲,
"該当なし"
)

=XLOOKUP(
検索条件1&"|"&検索条件2,
検索列1&"|"&検索列2,
戻り値範囲,
"該当なし"
)

XLOOKUPが重い時はどうすればいいですか？

XLOOKUPをもっと実務で使いこなしたい方へ(PR)

この記事ではXLOOKUPの基本から応用までを紹介しました。

ただ、実際の業務では

• VLOOKUPとの使い分け
• INDEX・MATCHとの違い
• FILTER関数との組み合わせ
• 実務での検索表作成

までまとめて理解すると、さらに作業効率が上がります。

動画で体系的に学びたい方は、以下の講座も参考になります。

• 【Excel VLOOKUP・HLOOKUP・XLOOKUPの基本からINDEX-MATCH関数までを学ぶ短期集中コース】（Udemy・PR） 
• 【Excelショートコース-Excelでサクッと検索＆集計！VLOOKUP/HLOOKUP/XLOOKUPの極意】（Udemy・PR）

※本リンクにはアフィリエイトリンク（PR）が含まれます。

まとめ：XLOOKUPを目的に合わせて使い分けよう

XLOOKUP関数は、検索範囲から条件に一致する値を探し、対応するデータを取得できる関数です。

VLOOKUPと比べて、

• 左右どちらの方向にも検索できる
• 列を挿入しても参照が崩れにくい
• 見つからない場合の表示を指定できる
• 上からだけでなく下からも検索できる

といった特徴があります。

基本的な検索ではXLOOKUPを使い、複数の結果を一覧で取得したい場合はFILTER、条件に合う値を合計したい場合はSUMIFやSUMIFSを使うなど、目的に応じて関数を選ぶことが大切です。

【関連記事】

• XLOOKUPで値が見つからない原因と対処法まとめ｜#N/Aや空白エラーを防ぐには？
• XLOOKUP関数で複数条件を扱う方法｜FILTER・INDEXとの違いもわかりやすく解説
• XLOOKUP関数が重い・遅い原因と対処法まとめ｜高速化のやり方も解説！
• XLOOKUP・FILTER・INDEX＋MATCHの違いと使い分けを実例付きで徹底解説