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

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

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

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

この記事では、XLOOKUP関数の基本的な使い方から、よくあるトラブルの原因と対処法までを初心者向けにやさしく解説します。
記事内では以下のような疑問にも触れています。

• 「XLOOKUPってどのバージョンから使えるの？」

• 「見つからないのに #N/A が出ない／空白にならない」

• 「重複データや複数条件で使うには？」

• 「エラーや表示形式の調整はどうする？」

さらに、より詳しく知りたい方向けに関連記事リンクも用意しています。
これからXLOOKUPを本格的に活用したい方のスタートガイドとして、ぜひ参考にしてみてください！

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! 対策：空白セルを確保する／結合セルを解除する

XLOOKUPで「見つからない」「#N/Aになる」などの具体的な原因と対処法は、以下の記事で詳しく解説しています。

→XLOOKUPで値が見つからない原因と対処法まとめ｜#N/Aや空白エラーを防ぐには？

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

XLOOKUPの結果に 0 が表示されてしまうと、「本当に0なのか」「エラーなのか」が分かりづらいことがあります。

その場合は、結果が0のときだけ空白にするのがおすすめです。

▼基本（IF関数で0だけ空白にする）

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

※同じXLOOKUP関数を2回書くため、データが大きいと少し重くなる場合があります。

▼おすすめ（LET関数で1回だけ計算して軽くする）

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

LET関数を使うと、XLOOKUP関数の計算結果を一度変数に入れられるため、式が読みやすくなり処理も軽くなります。

※LET関数もXLOOKUP関数同様、Microsoft 365 / Excel 2021以降で利用できます

■ 表示例（エラー時に空白）：

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

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

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

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

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

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

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

■ 複数条件の検索には？

=XLOOKUP(検索条件1 & 検索条件2, 結合済み検索列, 戻り値範囲)

※「&」で結合した検索キーを使って、一意の値に変換して検索します。

実務でよく使う複数条件検索の具体例は、こちらで図解付きで解説しています。

→XLOOKUP関数で複数条件を扱う方法｜FILTER・INDEXとの違いもわかりやすく解説

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

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

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

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

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

詳しくは以下の記事もご参考いただけます。

▶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関数 が得意です。
（※このあたりは複数条件・一覧抽出の話にもつながるので、詳しくは関連記事で解説します）

Filter関数については以下の記事にて詳しく紹介しています。

▶Excel FILTER関数の使い方まとめ｜複数条件・別シート・使えない時の対処法も解説！【事務作業がグッと楽に】

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

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

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

• 配列の扱い

• エラー表示の挙動

• 関数の更新状況

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

【関連記事】

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