6症状で直すXLOOKUP関数の使い方｜別シートと0対策

ExcelでVLOOKUPからXLOOKUPに乗り換えてみたものの、途中で手が止まっていませんか。

別シートに置いたマスタを、どう指定すればいいのか分からない
値は返ってきたのに、空欄のはずのセルが「0」になる
入社日を引いたら「44105」という5桁の数字が出てきた

エラーが表示されるなら、まだ調べようがあります。やっかいなのはエラーが出ないまま結果だけが変なケース。数式は正しく見えるので、どこを直せばいいのか見当もつきません。

この記事ではExcelのXLOOKUP関数の使い方を、別シートのマスタを参照するところまで通しで解説します。そのうえで「エラーは出ないのに結果が変」な6症状を切り分けていきましょう。症状 → 起きていること → 30秒でできる確認 → 対処の順に進めますよ。

対応バージョン: XLOOKUP関数が使えるのは次の環境です。
– Excel for Microsoft 365 / Excel 2024 / Excel 2021（いずれもMac版を含む）
– Excel for the web / iPad版 / iPhone版 / Android版
Excel 2016 と Excel 2019 は非対応。入力しても #NAME? エラーになります。

そもそも「使えない」「#NAME? が出る」で止まっていませんか。その場合はExcelのXLOOKUP・FILTERが使えない・#NAME?の原因と対処が答えになります。本記事ではその原因を深掘りしません。

Googleスプレッドシートをお使いなら、スプレッドシートのXLOOKUP関数の使い方のほうが近道ですよ。

ExcelのXLOOKUP関数とは？基本の書き方と必須の3引数
自分のシートで書く：範囲の指定はここでつまずく
エラーは出ないのに結果が変：症状別の切り分け早見表
エラーが表示されるときの対処（#N/A・#VALUE!・#SPILL!・#REF!）
XLOOKUPだから書ける使い方6パターン
VLOOKUPからの書き換え早見表（4パターン）
よくある質問（FAQ）
まとめ

ExcelのXLOOKUP関数とは？基本の書き方と必須の3引数

XLOOKUPは「エックスルックアップ」と読みます。やっていることはVLOOKUPと同じで、「この値を探して、対応するデータを持ってきて」と指示を出す関数。

名前の由来もシンプルです。VLOOKUPの「V」はVertical（縦）、HLOOKUPの「H」はHorizontal（横）の頭文字。XLOOKUPは縦の表でも横の表でも使えるため、方向を示す文字が付いていません。

大きく違うのは、列番号を数えなくていいことです。「A列で探して、B列を返す」とそのまま書けます。

まずは1本書いてみる（社員番号から氏名を引く）

この記事では、次の社員マスタを共通のサンプルとして使います。1行目が見出しで、データは2行目から6行目に入っている想定です。

	A列（社員番号）	B列（氏名）	C列（部署）	D列（内線）	E列（入社日）
2行目	E001	田中実	営業部	1201	2019/4/1
3行目	E002	鈴木花子	総務部	1305	2020/10/1
4行目	E003	佐藤健	経理部	（空欄）	2021/4/1
5行目	E004	高橋圭	営業部	1210	2023/4/1
6行目	E005	伊藤彩	情報システム部	1420	2024/9/1

社員番号「E002」から氏名を取り出す数式がこちら。

=XLOOKUP("E002", A2:A6, B2:B6)

結果は「鈴木花子」になります。数式の意味はシンプルで、A2:A6 の中から “E002” を探すだけ。見つかった位置と同じ場所にある B2:B6 の値が返ってきます。

実際に打つときの流れも見ておきましょう。

結果を出したいセルに =XLOOKUP( と入力する
探したい値が入ったセルをクリックし、カンマを打つ
検索範囲をドラッグして選び、カンマを打つ
戻り範囲をドラッグして選び、) で閉じてEnter

マウスで範囲を選べば、書式はExcelが自動で入れてくれます。手打ちで覚える必要はありませんよ。

6つの引数と、最初に覚えるべき3つ

XLOOKUPの構文はこちらです。

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

引数は全部で6つ。ただし必須は最初の3つだけで、残りは省略できます。

引数	必須／省略可	意味	指定例
検索値	必須	探したい値。セル参照でも直接入力でも可	`F2`
検索範囲	必須	検索値を探す範囲（1行または1列）	`A2:A200`
戻り範囲	必須	結果として返したい範囲	`B2:B200`
見つからない場合	省略可	該当なしのときに表示する値	`"該当なし"`
一致モード	省略可	一致のさせ方（0／-1／1／2）	`0`
検索モード	省略可	検索する方向（1／-1／2／-2）	`1`

一致モードの既定値は 0、つまり完全一致です。ここはVLOOKUPと正反対なので覚えておきましょう。

VLOOKUPは第4引数を省略すると近似一致になります。近似一致とは「だいたい近い値を拾う」動作のこと。指定を忘れて意図しない値が返る、というトラブルの定番でした。

XLOOKUPは何も指定しなければ完全一致。省略ミスによる事故が起きにくい設計です。

見つからないときの表示を決める（第4引数）

存在しない社員番号を検索してみます。

=XLOOKUP("E999", A2:A6, B2:B6, "該当なし")

「E999」はマスタにないため、結果は「該当なし」。第4引数を省略した場合は #N/A が返ります。

VLOOKUPでは、この処理をIFERROR関数で丸ごと包む必要がありました。XLOOKUPなら引数ひとつで済むので、数式がぐっと短くなります。

「そもそもXLOOKUPに乗り換えるべきか」で迷っている段階かもしれません。その場合は先にVLOOKUP・XLOOKUP・INDEX MATCH使い分け完全ガイドを読むほうが早道です。この記事は「使うと決めた人が、動く数式を書き上げる」ための解説になります。

自分のシートで書く：範囲の指定はここでつまずく

構文を覚えても、自分のシートに当てはめた瞬間に手が止まる。その原因はほぼ範囲の指定にあります。

実務のマスタは、同じシートに置いてあるとは限りません。別シート、別ブック、テーブル。パターンごとに書き方を押さえておきましょう。

同じシートのマスタを参照する

同じシート内なら、範囲をそのまま指定するだけです。

=XLOOKUP(F2, $A$2:$A$200, $B$2:$B$200, "該当なし")

$ を付けているのは、数式を下方向にコピーしても範囲がずれないようにするため。範囲を選んでから F4キー を押すと、$ が一括で付きます。

検索値の F2 には $ を付けません。行ごとに違う値を探したいので、ここは相対参照のままにしておきます。

別シートのマスタを参照する（シート名!範囲の書き方）

別シートを参照するときは、範囲の前に シート名! を付けます。シート名が「社員マスタ」なら次のとおり。

=XLOOKUP(F2, 社員マスタ!$A$2:$A$200, 社員マスタ!$B$2:$B$200, "該当なし")

検索範囲と戻り範囲の両方に、シート名が必要です。片方だけに付けると、もう片方は数式を書いているシートを見にいってしまいます。ここは見落としやすいので気をつけてください。

入力のコツはひとつだけ。数式を書いている途中でシート見出しをクリックし、そのまま範囲をドラッグすると、シート名込みで自動入力されます。手打ちより確実ですよ。

シート名にスペースや記号が入っているとき

シート名に半角スペースや記号が含まれる場合、シート名をシングルクォート ' で囲む必要があります。

=XLOOKUP(F2, '社員 マスタ'!$A$2:$A$200, '社員 マスタ'!$B$2:$B$200, "該当なし")

囲みが必要になるのは、たとえば次のようなシート名。

半角スペースを含む（社員マスタ）
ハイフンやカッコを含む（社員-マスタ、マスタ(2026)）
数字から始まる（2026年度マスタ）

これもマウスで範囲選択すれば、Excelが自動でクォートを付けてくれます。手打ちしてエラーを出すくらいなら、クリックで入れるほうが安全でしょう。

別ブックのマスタを参照するときの注意

別のExcelファイルにあるマスタを参照する場合は、ブック名を角カッコで囲みます。

=XLOOKUP(F2, '[社員マスタ.xlsx]名簿'!$A$2:$A$200, '[社員マスタ.xlsx]名簿'!$B$2:$B$200, "該当なし")

ここでの鉄則は、参照先のブックを開いた状態で数式を作ること。開いていればマウスで範囲を選べるので、書式を間違えません。

参照先を閉じた状態での挙動は、情報源によって説明が分かれています。確実を期すなら、両方のブックを開いた状態で結果を確認しておきましょう。

毎回開くのが面倒なら、マスタを同じブックの別シートにコピーしてしまう手もあります。運用としてはそのほうが安定します。

テーブル（構造化参照）で書くと行が増えても直さなくていい

マスタを「テーブル」に変換しておくと、範囲を列名で指定できます。テーブルとは、Excelの「テーブルとして書式設定」で作る表のこと。行の増減に自動で追従してくれます。

テーブル名が「社員マスタ」なら、こう書けます。

=XLOOKUP(F2, 社員マスタ[社員番号], 社員マスタ[氏名], "該当なし")

この書き方が便利なのは、マスタに行を追加したときです。$A$2:$A$200 のような固定範囲だと、201行目を追加した瞬間に検索対象から漏れます。テーブルなら範囲が自動で広がるため、数式を直す必要がありません。

列名で読めるので、他人が見ても意味が分かる。この読みやすさも見逃せないメリットですね。

A:A の全列参照をやめたほうがいい理由

A:A のような全列参照は、書くのがラクなので使われがちです。ただし実務のシートでは避けたほうが無難。

理由は主に2つあります。

重くなる: Excelの1列は1,048,576行。A:A&","&C:C のような配列演算と組み合わせると、100万行超の配列を毎回作ることになります
余計な行を巻き込む: 表の下に「合計」行や集計メモがあると、それも検索対象に入ってしまいます

行数を区切った範囲か、テーブル参照。このどちらかにしておけば、ファイルが重くなる原因をひとつ減らせます。

エラーは出ないのに結果が変：症状別の切り分け早見表

ここからが本題です。XLOOKUPで本当に困るのは、#N/A のようなエラーではありません。エラーが出ないまま、値だけが間違っている状態です。

まずは早見表で、自分の症状を探してみてください。

【早見表】症状 → 起きていること → 30秒でできる確認 → 対処

症状	起きていること	30秒でできる確認	対処
空欄のはずが `0` になる	戻り範囲の空セルを参照している	参照先のセルをクリックして中身を見る	戻り範囲を `IF(範囲="","",範囲)` で包む
日付が5桁の数字になる	結果セルの表示形式が「標準」のまま	結果セルの表示形式を確認する	表示形式を「短い日付形式」に変える
更新したのに古い値が返る	同じキーが複数行あり、先頭の1件を返している	`=COUNTIF(検索範囲, 検索値)` で件数を数える	第6引数に `-1` を指定する
数字なのに一致しない	文字列と数値・余分なスペース・全角半角の違い	`=検索値セル=マスタ側セル` が `TRUE` か見る	TRIM／VALUE／ASC で揃える
1件しか返らない	XLOOKUPは該当1件だけを返す関数	上と同じ COUNTIF で2件以上あるか見る	FILTER関数に置き換える
コピーすると結果がずれる	範囲が相対参照のまま	ずれたセルの数式で範囲の行番号を見る	F4で `$` を付ける／テーブル参照にする

この6つで、実務で出会う「変な結果」のかなりの部分をカバーできます。ここからは1つずつ詳しく見ていきましょう。

症状1：空欄のはずなのに0が返る

サンプルの社員マスタで、佐藤健さん（E003）の内線は空欄でした。ここを引いてみます。

=XLOOKUP("E003", $A$2:$A$6, $D$2:$D$6, "該当なし")

期待するのは空欄。ところが結果は 0 になります。

原因は「検索に失敗したから」ではありません。検索自体は成功していて、空のセルを参照した結果が 0 として返っているだけ。Excelの数式で空セルを参照したときに、よく知られた挙動です。

つまり第4引数に "該当なし" をいくら指定しても、この 0 は消えません。ここが多くの人のハマりどころですね。

症状1の対処3手と副作用の比較表

対処法は主に3つあります。どれも「0を消す」点では同じですが、残る副作用がまったく違います。

手1：戻り範囲を IF で包む

=XLOOKUP("E003", $A$2:$A$6, IF($D$2:$D$6="", "", $D$2:$D$6), "該当なし")

戻り範囲の中身を先に判定し、空なら空文字列 "" に置き換えてから返す形です。

手2：数式の末尾に &”” を付ける

=XLOOKUP("E003", $A$2:$A$6, $D$2:$D$6, "該当なし")&""

いちばん短くて手軽。ただし次の表のとおり、副作用がいちばん大きい方法でもあります。

手3：表示形式のユーザー定義で0を隠す

結果セルを選び、セルの書式設定からユーザー定義に 0;-0;;@ を設定します。; で区切られた4つの区画が、左から「正の数」「負の数」「ゼロ」「文字列」に対応しています。

3つ目を空にすると、ゼロだけが画面に出なくなる仕組み。小数を表示したい列なら、0.0;-0.0;;@ のように書き換えてください。

3手を横並びで比較すると、選ぶ基準がはっきりします。

比較軸	手1：IF で包む	手2：末尾に `&""`	手3：表示形式 `0;-0;;@`
見た目	空欄になる	空欄になる	空欄に見える
返り値の型	空セルは空文字列。値があるセルは元の型のまま	すべて文字列になる（数値も `"1210"` に）	変わらない（`0` が残る）
集計への影響	SUMは文字列を無視するので合計は正しい	SUMの対象外になり合計が0になる	0として集計に含まれ、AVERAGEが下がる
並べ替えへの影響	数値のまま並ぶ	文字列として並ぶため数値順にならない	数値のまま並ぶ
COUNTA での扱い	空文字列も1件と数えられる	1件と数えられる	1件と数えられる
複数列の取得	複数列の範囲でも同じ形で包める	同じく書けるが、全列が文字列になる	数式に手を入れないので影響なし

選び方の目安はこうです。

後で合計や並べ替えをする列 → 手3（表示形式）が安全。値を壊しません
見た目だけ整えたい表示専用の列 → 手1。合計も正しく出せます
手2の &"" は、集計に使わない文字列項目に限定して使いましょう

ちなみに &"" が便利に見えるのは、内線番号のような「数値だが計算しない項目」だから。金額や数量に使うと、後工程で合計が合わなくなります。

なお「空白に見えるのに空白扱いされない」問題は、XLOOKUPに限った話ではありません。空白セルの5パターンを診断したExcelで空白に見えるのに空白判定されない原因と対処法もあります。あわせて読むと、原因の切り分けが一気にラクになりますよ。

症状2：日付を引いたら5桁の数字になった

鈴木花子さん（E002）の入社日を引いてみます。

=XLOOKUP("E002", $A$2:$A$6, $E$2:$E$6, "該当なし")

マスタの表示は「2020/10/1」。ところが結果セルには 44105 と出ます。

これは壊れているわけではありません。Excelは日付を「シリアル値」という連番で管理しています。

2020年10月1日を数値に直すと 44105。中身は正しく、表示形式だけが「標準」のままという状態です。

方法1：結果セルの表示形式を変える

ホームタブの数値グループで、表示形式を「短い日付形式」に変更します。これがいちばん素直な直し方。値も日付のまま保たれます。

方法2：TEXT関数で書式を固定する

=TEXT(XLOOKUP("E002", $A$2:$A$6, $E$2:$E$6), "yyyy/m/d")

結果は「2020/10/1」になります。ただしTEXT関数の戻り値は文字列です。

そのセルを使って日数計算をするとうまくいきません。日付として使い続けるなら、方法1を選んでください。

症状3：更新したのに古い行の値が返ってくる

受注履歴や更新ログのように、同じキーが何度も登場する表で起きる症状です。

=XLOOKUP(F2, $A$2:$A$200, $C$2:$C$200, "履歴なし")

新しい行を追記したのに、返ってくるのは古い値のまま。原因は検索モード（第6引数）の既定値にあります。

既定は 1、つまり先頭から末尾に向かって探す動作。最初に見つかった1件を返すため、下に追記していく表ではいちばん上の古い行が拾われてしまいます。

対処はかんたんで、第6引数に -1 を指定するだけ。

=XLOOKUP(F2, $A$2:$A$200, $C$2:$C$200, "履歴なし", 0, -1)

-1 は末尾から先頭に向かって検索する指定です。第6引数を使うときは、手前の第5引数の位置を飛ばせません。0（完全一致）も一緒に書いておきましょう。

値ではなく「何行目にあるか」を取りたい場面もありますよね。その場合はExcelのXMATCH関数の使い方が使えます。XMATCHにも同じ検索モードがあるため、考え方はそのまま応用できますよ。

症状4：数字に見えるのに一致しない

見た目はどう見ても同じ「1001」。なのに #N/A が返る、あるいは想定と違う行が返る。この症状の正体は、ほぼ型かスペースです。

30秒でできる確認から始めましょう。空いたセルに次の式を入れます。

=F2=$A$2

FALSE なら、見た目が同じでも中身は別物という証拠。さらに =LEN(F2) と =LEN(A2) の文字数を比べると、余分なスペースの有無まで分かります。

原因別の対処を整理しておきます。

原因	見分け方	対処
文字列と数値の混在	セル内で左寄せ（文字列）か右寄せ（数値）か	VALUE関数で数値に統一する
前後に半角スペース	LEN関数の文字数がマスタ側と違う	TRIM関数で除去する
全角スペースが混入	TRIMをかけても文字数が減らない	SUBSTITUTE関数で全角スペースを削除する
全角と半角の混在	「１００１」と「1001」が並んでいる	ASC関数で半角に統一する

ここで見落としやすいのが3行目。TRIM関数が消せるのは半角スペースだけで、全角スペースはそのまま残ります。TRIMをかけたのに直らないときは、全角スペースを疑ってください。

各関数の詳しい使い方は、ExcelのTRIM関数の使い方とVALUE関数の使い方で確認できます。マスタ側を一括でクリーニングしてから検索する。これがいちばん後腐れのない直し方です。

症状5：1件しか返らない／2件目以降が出てこない

「営業部の人を全員出したい」。この用途でXLOOKUPを使うと、必ず1件しか返りません。

これはバグでも設定ミスでもなく、仕様です。XLOOKUPは条件に合う1件を特定して返す関数。該当が10件あっても、返すのは1件だけ。

複数件を並べたいなら、そもそも使う関数が違います。

=FILTER($B$2:$B$200, $C$2:$C$200="営業部", "該当なし")

FILTER関数なら、条件に合う行をすべて縦に並べて返してくれます。詳しい書き方はFILTER関数の使い方にまとめました。

「1件でいいが、最新の1件がほしい」なら症状3の -1。「全部ほしい」ならFILTER。この使い分けを覚えておくと迷いません。

症状6：数式をコピーすると結果がずれる

1行目のセルは正しいのに、下にコピーすると結果がおかしくなる。犯人は相対参照です。

=XLOOKUP(F2, A2:A6, B2:B6, "該当なし")

この式を1行下にコピーすると、範囲まで一緒に動いて A3:A7 と B3:B7 に変わります。検索対象が1行ずつ削られていくため、下の行ほど #N/A や違う値が増えていくわけです。

確認は10秒で終わります。おかしくなったセルをダブルクリックし、範囲の行番号が動いていないか見るだけ。

対処は2択です。

=XLOOKUP(F2, $A$2:$A$6, $B$2:$B$6, "該当なし")

ひとつは、範囲を選んで F4キー を押し、$ で固定する方法。もうひとつは、先に紹介したテーブル参照に切り替える方法です。テーブル参照なら $ を付ける必要すらありません。

エラーが表示されるときの対処（#N/A・#VALUE!・#SPILL!・#REF!）

ここまでは「エラーが出ない」症状でした。ここからは、はっきりエラーが表示されるケースを片付けていきます。

#N/A：見つからない（第4引数で表示を制御する）

#N/A は「検索値が見つからなかった」という意味のエラーです。まずは第4引数で、表示そのものを制御しましょう。

=XLOOKUP(F2, $A$2:$A$200, $B$2:$B$200, "該当なし")

ただし表示を変えるのと、原因をつぶすのは別の話。本当に存在しないのか、それとも症状4の型・スペース問題なのか。ここは切り分けておく必要があります。

検索値が空欄のときにも #N/A は出ます。入力前の行まで「該当なし」で埋まるのが気になるなら、"" を指定して空欄に見せる手もありますよ。

#VALUE!：検索範囲と戻り範囲のサイズが合っていない

検索範囲と戻り範囲の行数がそろっていないと #VALUE! になります。

=XLOOKUP(A2, B2:B100, C2:C50)

検索範囲 B2:B100 は99行、戻り範囲 C2:C50 は49行。行数が違うため、どの行を返せばいいのか決まりません。両方の開始行と終了行をそろえれば解消します。

マウスでドラッグして範囲を選び直すのが確実。手打ちで行番号を直すと、また別のずれを生みがちですから。

#SPILL!：結果を書き出す先が空いていない

戻り範囲に複数列を指定すると、結果は隣のセルへ自動的にあふれ出ます。これがスピル（1つの数式の結果が複数のセルに展開される動き）です。

そのあふれ先にすでにデータが入っていると #SPILL! になります。対処は、展開先のセルを空けるだけ。

もうひとつ、テーブルの中に置いた数式もつまずきます。Excelはテーブル内でのスピルをサポートしていないため、複数列を返す数式はテーブルの外のセルに置くのが原則。

テーブル内で #SPILL! が出たら、まず同じ数式をテーブルの外へ移して確かめてみてください。

#REF!：参照していた行・列が消えた

#REF! は参照先が失われたときのエラーです。マスタの列を削除した、シートごと消した。こうした操作が引き金になります。

XLOOKUPは列番号を使わないため、VLOOKUPより参照崩れには強い設計。それでも参照先そのものが消えれば、当然エラーになります。

直前の操作を元に戻すのが最短ルート。原因の切り分け方はExcel参照エラーの直し方にまとめてあります。

#NAME? が出た場合

#NAME? はXLOOKUPに対応していないバージョンで開いたときに出ます。数式の書き方ではなく、環境の問題。

原因と対処の詳細はExcelのXLOOKUP・FILTERが使えない・#NAME?の原因と対処にまとめているので、そちらをご覧ください。

ここまでで扱っていないエラー値に出会ったら、Excelエラー値12種類の原因と対処法一覧から探すのが早道です。

XLOOKUPだから書ける使い方6パターン

基本とトラブル対処を押さえたら、次は「VLOOKUPでは書けなかった形」に進みましょう。ここがXLOOKUPを使う本当のメリットです。

1. 左側の列を引く（VLOOKUPでは書けなかった形）

氏名から社員番号を引く、つまり右の列で探して左の列を返すパターン。VLOOKUP単体では不可能でした。

=XLOOKUP(F2, $B$2:$B$6, $A$2:$A$6, "該当なし")

検索範囲と戻り範囲を入れ替えるだけ。左でも右でも書き方は変わりません。マスタの列順を気にしなくてよくなるのは、地味ですが効きます。

2. 1本の数式で複数列をまとめて取り出す

氏名・部署・内線を横並びで取り出したいケースです。

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

F2に「E004」が入っていれば、「高橋圭」「営業部」「1210」の3つが横3セルに展開されます。VLOOKUPなら列番号を変えた数式を3本並べる必要がありました。

展開先のセルは空けておきましょう。埋まっていると #SPILL! になります。

3. 重複がある表から一番下（最新）の行を取り出す

症状3で扱った検索モード -1 は、応用としても使えます。

=XLOOKUP(F2, $A$2:$A$200, $C$2:$C$200, "履歴なし", 0, -1)

追記型の履歴シートから最新の1件を拾う、定番の書き方。並べ替えも補助列も不要です。

4. 「以上・以下」で近い値を探す（一致モード -1 / 1）

送料テーブルのような「しきい値表」との照合です。次の表を想定します。

	A列（購入金額の下限）	B列（送料）
2行目	0	800
3行目	3000	500
4行目	5000	0

購入金額4,200円の送料を求めてみましょう。

=XLOOKUP(4200, $A$2:$A$4, $B$2:$B$4, "", -1)

4200に完全一致する行はありません。一致モード -1 は「なければ次に小さい項目」を拾います。そのため3000の行がヒットし、結果は 500 になります。

逆に 1 を指定すると「次に大きい項目」を探します。しきい値表は下限値の昇順に並べて作っておくと、結果の確認がラクですよ。

なお、検索モード（第6引数）の 2 / -2 は別物です。こちらは並べ替え済みのデータが前提。

公式にも「並べ替えられていない場合、無効な結果になる」と明記されています。ソートに自信がなければ、既定の 1 のままにしておきましょう。

5. ワイルドカードで部分一致検索する（一致モード 2）

「東京を含む住所」のような部分一致には、一致モード 2 を使います。

=XLOOKUP("*東京*", $A$2:$A$100, $B$2:$B$100, "該当なし", 2)

検索キーワードをセルから受け取る形にすると、実務で使いやすくなります。

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

使える記号は3つ。

*：任意の文字列（0文字以上）
?：任意の1文字
~：直後の * や ? を、記号そのものとして扱う

6. 2つの条件を組み合わせて検索する

同姓同名が別々の部署にいる。そんなときに「部署と氏名の両方が一致する行」を探すパターンです。VLOOKUPでは補助列を作るしかありませんでした。

=XLOOKUP(F2&"／"&G2, $C$2:$C$200&"／"&$B$2:$B$200, $A$2:$A$200, "該当なし")

F2に部署、G2に氏名を入れる想定。検索値と検索範囲を、それぞれ同じ順番・同じ区切り文字で連結するのがコツです。

区切り文字を挟むのには理由があります。区切りなしで連結すると、「AA」+「B」と「A」+「AB」がどちらも「AAB」になってしまう。別の行を誤ってヒットさせないための保険というわけです。

この書き方は全列参照との相性が最悪です。C:C&"／"&B:B と書くと、100万行超の配列を毎回作ることになります。

必ず行数を区切った範囲を指定してください。うまく結果が出ないときは、まず範囲を数百行まで絞って試すのが近道でしょう。

VLOOKUPからの書き換え早見表（4パターン）

手元のVLOOKUP数式を書き換えるときの対応表です。VLOOKUP側の引数をもう一度確認したい方へ。先にExcelのVLOOKUP関数の使い方を開いておくと読みやすくなります。

前提のデータはこれまでと同じ。A列＝社員番号、B列＝氏名、C列＝部署、D列＝内線です。検索値はF2セルに入れる想定で読み進めてください。

パターン1：単純な置き換え

社員番号から氏名を引く、最も多い形。

Before（VLOOKUP）

=VLOOKUP($F2, $A$2:$C$200, 2, FALSE)

After（XLOOKUP）

=XLOOKUP($F2, $A$2:$A$200, $B$2:$B$200)

列番号の 2（A列から数えて2列目＝B列）が、戻り範囲 $B$2:$B$200 に置き換わります。完全一致を指定する FALSE も不要。XLOOKUPは既定が完全一致だからです。

パターン2：左方向の検索に置き換える

氏名から社員番号を引く形。VLOOKUP単体では書けず、INDEX+MATCHを使っていた部分です。

Before（INDEX+MATCH）

=INDEX($A$2:$A$200, MATCH($F2, $B$2:$B$200, 0))

After（XLOOKUP）

=XLOOKUP($F2, $B$2:$B$200, $A$2:$A$200)

関数が2つから1つに減り、読む順番も自然になりました。

パターン3：IFERROR で包んでいた式を第4引数に統合する

見つからないときに空欄を返す処理です。

Before（VLOOKUP＋IFERROR）

=IFERROR(VLOOKUP($F2, $A$2:$C$200, 2, FALSE), "")

After（XLOOKUP）

=XLOOKUP($F2, $A$2:$A$200, $B$2:$B$200, "")

外側の包みが消えて、第4引数に収まりました。

ひとつ違いがあります。IFERRORはあらゆるエラーを握りつぶすのに対し、第4引数が受け持つのは「見つからない場合」だけ。範囲サイズの不一致による #VALUE! などは、そのまま表示されます。

これはむしろメリットでしょう。本当の設定ミスが隠れずに見つかりますからね。

パターン4：複数列を1本の数式にまとめる

氏名・部署・内線を、それぞれ別の数式で引いていたケースです。

Before（VLOOKUP・3本必要）

=VLOOKUP($F2, $A$2:$D$200, 2, FALSE)
=VLOOKUP($F2, $A$2:$D$200, 3, FALSE)
=VLOOKUP($F2, $A$2:$D$200, 4, FALSE)

After（XLOOKUP・1本）

=XLOOKUP($F2, $A$2:$A$200, $B$2:$D$200)

列番号2・3・4が、そのまま戻り範囲 $B$2:$D$200（B列・C列・D列）に対応します。数式が1本になるので、修正箇所も1か所で済みます。

4パターンをまとめておきます。

パターン	Before	After	変わるポイント
1. 単純置換	`=VLOOKUP($F2, $A$2:$C$200, 2, FALSE)`	`=XLOOKUP($F2, $A$2:$A$200, $B$2:$B$200)`	列番号 → 戻り範囲。`FALSE` 不要
2. 左方向	`=INDEX($A$2:$A$200, MATCH($F2, $B$2:$B$200, 0))`	`=XLOOKUP($F2, $B$2:$B$200, $A$2:$A$200)`	関数2つ → 1つ
3. IFERROR統合	`=IFERROR(VLOOKUP($F2, $A$2:$C$200, 2, FALSE), "")`	`=XLOOKUP($F2, $A$2:$A$200, $B$2:$B$200, "")`	包み → 第4引数
4. 複数列	数式3本（列番号2・3・4）	`=XLOOKUP($F2, $A$2:$A$200, $B$2:$D$200)`	3本 → 1本にスピル

なお、既存のVLOOKUPをどこまで書き換えるかという判断は、この記事では扱っていません。選び方はVLOOKUP・XLOOKUP・INDEX MATCH使い分け完全ガイドにフロー形式でまとめています。

よくある質問（FAQ）

XLOOKUPは古いExcelで開くとどうなりますか？

#NAME? エラーになります。Excel 2016 と Excel 2019 はXLOOKUPに対応していないためです。

なお Office 2016 と Office 2019 は、2025年10月14日に延長サポートを終了しました。アプリ自体は動きますが、セキュリティ更新やバグ修正はもう提供されません。原因の詳しい仕組みは、前章の「#NAME? が出た場合」で紹介した記事をご覧ください。

VLOOKUPは全部XLOOKUPに書き換えるべきですか？

困っていないなら、急いで書き換える必要はありません。新しく作る数式からXLOOKUPに切り替えるだけでも十分に効果があります。

判断の基準はバージョン・共有相手・用途の3点。VLOOKUP・XLOOKUP・INDEX MATCH使い分け完全ガイドで確認してみてください。

XLOOKUPとINDEX+MATCHはどちらが速いですか？

一概には言えません。体感速度は表の大きさや数式の本数、参照範囲の広さで大きく変わります。

それよりも効くのが範囲の指定です。A:A の全列参照をやめて必要な行数に絞るほうが、関数を選び直すより効いてきますよ。

Googleスプレッドシートでも同じように使えますか？

スプレッドシートにもXLOOKUP関数があり、引数の構成はExcelとほぼ同じです。ただし細かい挙動や関連関数の対応状況には差があります。

Sheets側の書き方はスプレッドシートのXLOOKUP関数の使い方にまとめてあります。

0の代わりに空欄にすると、後で合計できなくなりませんか？

IF(範囲="","",範囲) 方式なら、SUM関数は文字列を無視するので合計は正しく出ます。ただし COUNTA では1件として数えられる点に注意。

集計や並べ替えを重視するなら、値を 0 のまま残す表示形式方式（0;-0;;@）が安全です。詳しくは症状1の比較表を見返してみてください。

まとめ

ExcelのXLOOKUP関数の要点を振り返ります。

基本は =XLOOKUP(検索値, 検索範囲, 戻り範囲) の3引数。列番号を数える必要はない
別シートは シート名!範囲、シート名に空白や記号があれば ' で囲む
A:A の全列参照は重くなるので、行数を区切るかテーブル参照にする
空欄が 0 になるのは検索の失敗ではない。対処法は3つあり、副作用が違う
日付が5桁の数字になるのは表示形式の問題。値は壊れていない
古い行が返るなら第6引数に -1、複数件ほしいならFILTER関数

XLOOKUPでつまずく原因は、関数そのものより範囲の指定と表示形式にあることがほとんど。エラーが出ないまま結果が変なときは、この記事の早見表から症状を探してみてください。

まずは1本、別シートのマスタを参照する数式を書いてみましょう。そこが動けば、残りは応用でつながっていきますよ。