XLOOKUP と VLOOKUP のどちらを選ぶかについては、ファイルを開くすべての人が Microsoft 365 の Excel、Excel 2021 以降、または Excel for the web を使っているなら XLOOKUP を使うのがおすすめです。VLOOKUP でできることはすべて、より少ないミスでこなせます。既定で完全一致になり、左方向にも検索でき、列を挿入しても壊れません。VLOOKUP を使うのは、ブックを Excel 2019 以前でも動かす必要がある場合だけにしましょう。
この互換性が最大の注意点です。XLOOKUP は Excel 2016 や 2019 には存在しないため、XLOOKUP の数式だらけのファイルは、それらのバージョンを使っている人には #NAME? エラーとして表示されます。
なお、数式は英語版の Excel と同じ形で示しています（日本語版の Excel でも、VLOOKUP や XLOOKUP などの関数名は英語のまま使えます）。また、サンプルの表は図と同じく英語のまま掲載しています。
| VLOOKUP | XLOOKUP | |
|---|---|---|
| 左方向の検索（検索列より左の列を返す） | できない | できる |
| 既定の検索方法 | 近似一致（FALSE を付けない場合） | 完全一致 |
| 列の挿入・削除への強さ | 弱い（列番号が固定のまま） | 強い（範囲が自動調整される） |
| 「見つからない」場合のメッセージ | IFNA や IFERROR が必要 | 標準搭載（if_not_found） |
| 複数の列を一度に返す | 簡単にはできない | できる（結果がスピルする） |
| 最後の一致から上向きに検索 | できない | できる（search_mode -1） |
| 近似一致 | データを昇順に並べておく必要がある | 次に小さい値または大きい値。並べ替え不要 |
ワイルドカード（*、?） | 使える（完全一致の場合） | 使える（match_mode 2 の場合） |
| 横方向の検索 | できない（HLOOKUP が必要） | できる（同じ関数） |
| 速度 | 一般的なブックでは実用上の差はない | 同じ。並べ替え済みデータではバイナリ検索も選べる |
| 利用できるバージョン | すべてのバージョンの Excel | Microsoft 365、Excel 2021、Excel 2024、Excel for the web、モバイル アプリ |
構文を並べて比較
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
大きな違いは、答えのある場所を Excel に伝える方法です。VLOOKUP は表全体と、その表の左端から数えた列番号を指定します。XLOOKUP は、検索する列と、値を返す列という 2 つの別々の範囲を指定します。
同じ検索を 2 通りで書く
A1:D6 に小さな商品リストがあり、G2 に商品 ID を入力してあるとします。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product ID | Product | Category | Price |
| 2 | P-101 | Wireless Mouse | Accessories | $24.99 |
| 3 | P-102 | USB-C Hub | Accessories | $39.50 |
| 4 | P-103 | Laptop Stand | Office | $45.00 |
| 5 | P-104 | Webcam HD | Video | $59.00 |
| 6 | P-105 | Desk Lamp | Office | $32.75 |
P-104 の価格を調べるには、次のようにします。
=VLOOKUP(G2, A2:D6, 4, FALSE)
=XLOOKUP(G2, A2:A6, D2:D6)
どちらも $59.00 を返します。VLOOKUP は A2:D6 の最初の列を検索して 4 列目を返し、FALSE で完全一致を指定します。XLOOKUP は A2:A6 を検索し、D2:D6 から一致するセルを返します。完全一致が既定です。
数式を列の下方向にコピーする場合は、=XLOOKUP(G2, $A$2:$A$6, $D$2:$D$6) のように、絶対参照で表を固定します。参照の中にカーソルがある状態で、Windows では F4、Mac では Cmd+T を押すと、$ が付きます。
VLOOKUP の 2 つの典型的な落とし穴
1. FALSE（または 0）を付け忘れる
VLOOKUP の最後の引数は省略できますが、省略すると Excel は近似一致を使います。近似一致では、VLOOKUP は最初の列が並べ替え済みであるものと見なし、検索値以下で最も近い値を、何の警告もなく返します。
上の表で =VLOOKUP("P-110", A2:D6, 4) を試してみてください。P-110 は存在しませんが、#N/A ではなく $32.75 が返ります。「P-105」が最も近い小さい値であるため、P-105 の価格が返されるのです。並べ替えられていないデータでは、結果がさらにでたらめになることもあります。
完全一致の VLOOKUP は、必ず FALSE または 0 で終わらせましょう。XLOOKUP は、特に指定しない限り完全一致を使うので、この問題は起こりません。
2. 固定された列番号
VLOOKUP の数式にある 4 は、ただの数字です。誰かが Product と Category の間に Supplier 列を挿入すると、表は A:E になり、Price は E 列に移りますが、数式は相変わらず 4 列目を返します。その 4 列目は今は Category です。$59.00 の代わりに「Video」と表示されてしまいます。
XLOOKUP は価格の列そのもの（D2:D6）を指しているため、Excel が自動的に E2:E6 に更新し、答えは正しいままです。
VLOOKUP にはできない XLOOKUP の機能
左方向に検索する
VLOOKUP が返せるのは、検索列より右にある列だけです。XLOOKUP は方向を問いません。G2 の商品名から商品 ID を調べるには、次のようにします。
=XLOOKUP(G2, B2:B6, A2:A6)
G2 が「Desk Lamp」なら、A 列が B 列の左側にあっても、P-105 が返されます。
見つからない場合に分かりやすいメッセージを表示する
=XLOOKUP(G2, A2:A6, D2:D6, "Not found")
VLOOKUP で同じことをするには、関数で包む必要があります：=IFNA(VLOOKUP(G2, A2:D6, 4, FALSE), "Not found")。
1 つの数式で複数の列を返す
=XLOOKUP(G2, A2:A6, B2:D6)
P-104 の場合、Webcam HD、Video、$59.00 が横に並んだ 3 つのセルにスピルします。それらのセルは空にしておいてください。そうしないと #SPILL! エラーになります。
最初の一致ではなく最後の一致を探す
同じ顧客が何度も登場するログでは、search_mode を -1 にすると、下から上に向かって検索し、最新のエントリを取得できます。
=XLOOKUP(G2, A2:A500, C2:C500, "Not found", 0, -1)
並べ替えなしの近似一致
段階的な価格や手数料率の場合、VLOOKUP の近似一致は、最初の列が昇順に並べ替えられているときにしか機能しません。XLOOKUP の match_mode -1（完全一致、なければ次に小さい項目）と 1（完全一致、なければ次に大きい項目）は、並べ替え済みのデータを必要としません。最低注文金額が J2:J5、割引が K2:K5 にある場合は次のようになります。
=VLOOKUP(B2, J2:K5, 2, TRUE)
=XLOOKUP(B2, J2:J5, K2:K5, , -1)
ワイルドカード
どちらも * と ? に対応していますが、XLOOKUP では、ワイルドカードとして扱うために match_mode 2 が必要です。
=VLOOKUP("Desk*", B2:D6, 3, FALSE)
=XLOOKUP("Desk*", B2:B6, D2:D6, , 2)
どちらも、Desk Lamp の $32.75 を返します。
INDEX/MATCH：昔ながらの代替手段
XLOOKUP が登場する前、VLOOKUP の制限を回避する標準的な方法は、INDEX と MATCH の組み合わせでした。左方向にも検索でき、列を挿入しても壊れず、すべてのバージョンの Excel で動作するため、Excel 2019 以前でも開く必要があるファイルには最適です。
=INDEX(D2:D6, MATCH(G2, A2:A6, 0))
=INDEX(A2:A6, MATCH(G2, B2:B6, 0))
1 つ目は G2 の ID に対応する価格を返し、2 つ目は左方向に検索する例です。MATCH の 0 は完全一致を意味し、VLOOKUP の FALSE と同じく、つい忘れがちです。XLOOKUP は、同じ考え方をより短く、より安全にしたものです。
VLOOKUP を XLOOKUP に変換する方法
標準の変換機能はありませんが、置き換えは機械的に行えます。
- VLOOKUP を探します。
Ctrl+F（Mac ではCmd+F）を押し、VLOOKUP と入力します。詳細な設定が隠れている場合は［オプション］をクリックし、［検索対象］を［数式］にして、［すべて検索］をクリックします。 - 最初の引数（検索値）はそのままにします。
- 表を、その最初の列に置き換えて
lookup_arrayにします。 - 列番号を、対応する列に置き換えて
return_arrayにします。A 列から始まる表の 5 列目は E 列です。 FALSEを削除します。元の数式がTRUEで終わっていた場合や、最後の引数がなかった場合は、「次に小さい値」の動作を維持するためにmatch_mode-1 を追加します。
例：
=VLOOKUP(A2, Products!$A$2:$F$500, 5, FALSE)
=XLOOKUP(A2, Products!$A$2:$A$500, Products!$E$2:$E$500)
シート全体を変換する前に、いくつかの結果を元の数式と照合してください。並べ替えられていないデータに対する近似一致の VLOOKUP は、最初から間違った値を返していた可能性があります。そのため、XLOOKUP の答えが正当に異なることもあります。
XLOOKUP のファイルを Excel 2016 や 2019 で開くとどうなるか
ブックは開きますが、それらのバージョンは XLOOKUP 関数を認識しません。数式バーには =_xlfn.XLOOKUP(...) と表示されます。最初は最後に保存された結果がセルに表示されていることもありますが、数式が再計算された時点で #NAME? が返されます。
古いバージョンを使っている人とファイルを共有する場合は、次のいずれかにします。
- 代わりに INDEX/MATCH または VLOOKUP を使う。
- 結果を値として貼り付けたコピーを送る（［コピー］→［形式を選択して貼り付け］→［値］）。
FILTER、UNIQUE、XMATCH など、ほかの新しい関数にも同じことが当てはまります。
Google スプレッドシートの XLOOKUP
Google スプレッドシートにも XLOOKUP があり、引数の順序も同じです：=XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])。この記事の数式はそのまま動作するため、Excel とスプレッドシートの間でやり取りするファイルでも、XLOOKUP は安心して使えます。
検索結果が #N/A や間違った値になる理由
- 余分なスペース。 末尾にスペースのある「P-104 」は、「P-104」と一致しません。
TRIMでデータを整えてください。 - 文字列として保存された数値。 文字列として入力された ID は、数値として保存された同じ ID とは一致しません。セルの隅にある緑色の三角形を探し、列を 1 つの型に統一してください。
- VLOOKUP の FALSE の付け忘れ。
#N/Aではなく、もっともらしいけれど間違った答えが返されます。 - 数式をコピーしたときに範囲がずれた。 検索する表には、
$の絶対参照を使ってください。 - VLOOKUP の
#REF!。 列番号が、表の列数より大きくなっています。 - XLOOKUP の
#VALUE!。 検索範囲と戻り値の範囲のサイズが違います（例：A2:A6 と D2:D60）。 #NAME?関数名のスペルが間違っているか、XLOOKUP のないバージョンでファイルを開いています。
よくある質問
XLOOKUP は VLOOKUP より優れていますか？ はい、ほとんどの用途でそうです。既定で完全一致になり、どの方向にも検索でき、列が移動しても壊れず、見つからない値を、関数で包まずに処理できます。VLOOKUP の唯一の本当の利点は、すべてのバージョンの Excel で動作することです。
XLOOKUP は VLOOKUP より速いですか？ 日常的なブックでは違いを感じないでしょう。並べ替え済みの非常に大きなリストでは、XLOOKUP のバイナリ検索モード（search_mode 2 または -2）で高速化できますが、データが並べ替えられていないと誤った結果が返されます。
XLOOKUP で #NAME? と表示されるのはなぜですか？ お使いのバージョンの Excel に XLOOKUP が含まれていません。Microsoft 365、Excel 2021 以降、Excel for the web には含まれていますが、Excel 2019 や 2016 にはありません。代わりに INDEX/MATCH を使ってください。
XLOOKUP は HLOOKUP の代わりにもなりますか？ はい。検索範囲と戻り値の範囲が、列ではなく行であれば、XLOOKUP は同じ構文で横方向に検索します。
XLOOKUP で最初の 1 件だけでなく、一致するものをすべて返せますか？ いいえ、返せるのは 1 件（または 1 行分の複数の列）だけです。一致するすべての行を取得するには、FILTER を使います（例：=FILTER(A2:D6, C2:C6="Office")）。
VLOOKUP はまだ覚える必要がありますか？ 古いブックやテンプレートで目にするので、見て分かるようにしておく価値はあります。新しく数式を作るときは XLOOKUP を覚え、古い Excel で開く必要があるファイルには INDEX/MATCH を念頭に置いておきましょう。
検索の数式を AI に書いてもらう
検索の数式は、FALSE の付け忘れや範囲のずれといった些細なことが、気づきにくいエラーを最も多く生む場所です。CoreGPT for Excel なら、「G2 の商品 ID に対応する価格を探して」や「各行が目標より上か下かを示して」のように、やりたいことを伝えるだけで、短い説明付きで数式をシートに書き込みます。無料で始められ、Windows、Mac、Web の Excel で利用できます。
CoreGPT次の数式を使って、下までコピーしてください：
=IF(C2>=D2,"達成","未達")今すぐ試す：CoreGPT for Excel
関連記事：
