XLOOKUP(): Excelで最も汎用的な検索関数

これまでにVLOOKUP()を使っていて、「左側を検索したいのにできない…」と行き詰まったことがあるなら、XLOOKUP()がその問題を解決します。これはExcelの新しい検索関数で、VLOOKUP()、HLOOKUP()、さらには多くのINDEX()とMATCH()の組み合わせまで、1つのシンプルな数式で置き換えられます。

XLOOKUP()は任意の方向に検索でき、同時に複数列を返せて、値が見つからない場合はカスタムメッセージを表示できます。

本ガイドでは、まず構文から説明し、その後は同じ小さなデータセットを通して、実際の数式をステップごとに作成していきます。完全一致と近似一致、複数列の返し方、エラー処理、そしてXLOOKUP()がVLOOKUP()やINDEX()/MATCH()とどう違うかも扱います。

ExcelでXLOOKUP()を使う方法

実際のXLOOKUP()数式を最初から作ってみましょう。

データセットには5つの製品があり、各製品にID、名前、カテゴリ、価格があり、列A〜Dに並び、1行目がヘッダーです。

XLOOKUP()用のデータセット。画像：著者

たとえば「P104」の価格を見つけたいとします。手順を追ってやってみましょう。

単一の値を検索する

結果を表示したい空のセルをクリックします。ここではF2を使います。次の数式を入力してください。

=XLOOKUP("P104", A2:A6, D2:D6)

この数式では、

• "P104" はlookup_value（検索したい値）

• A2:A6 はlookup_array（Excelが検索する範囲）

• D2:D6 はreturn_array（Excelが答えを返す範囲）

Excelは列A（Product ID）を検索し、"P104"を5行目で見つけ、対応する列Dの値、$45を返します。

"P104"を直接入力する代わりに、セル参照を使えば、数式自体を編集せずに検索値を変更できます。今回の例では、セルA5に"P104"があるので、数式は次のようになります。

=XLOOKUP(A5, A2:A6, D2:D6)

ExcelでXLOOKUP()を使う。画像：著者

これでA5に任意の製品IDを入力すると、価格が自動的に更新されます。

別の列の値を返す

ここがXLOOKUP()とVLOOKUP()の本当の違いです。検索列の右側の列からしか値を返せない、という制約がありません。

価格ではなく「P102」のカテゴリを取りたいとしましょう。セットアップは同じで、return_arrayを列C（Category）に向けるだけです。

=XLOOKUP(A3, A2:A6, C2:C6)

ここでは、A3なら「Home」を返します。A5に変えれば「Electronics」に更新されます。同様に、return_arrayを変更すれば、同じlookup_valueとlookup_arrayで、製品名や価格など任意の列を返せます。

XLOOKUP()で別の列から値を返す。画像：著者

VLOOKUP()では、検索列からの列数を数え、別の結果を返すたびに列番号を調整する必要がありました。XLOOKUP()なら、欲しい列を直接指し示すだけです。

数式を複数行にコピーする

ここまでは1つずつ値を検索してきました。しかし、実際のスプレッドシートでは、多くの値を一度に検索することがよくあります。例えば、価格が必要な注文IDの一覧がある場合などです。

設定方法は次のとおりです。列A（A2から）は既にProduct IDの一覧です。各IDの右隣、列Fに対応する価格を表示したいとします。

そのために、F2をクリックして次の数式を入力し、検索値にA2を参照します。

=XLOOKUP(A2, $A$2:$A$6, $D$2:$D$6)

次にもう一度F2をクリックし、セル右下の小さな四角（フィルハンドル）を、残りの行までドラッグします。

Excelが数式を下方向にコピーし、各行のProduct IDに対して正しい価格が返ります。A2のIDはF2に、A3のIDはF3に…という具合です。

数式を他のセルへコピー。画像：著者

A2:A6とD2:D6に付いているドル記号に注目してください。これは絶対参照と呼ばれ、範囲を固定して、数式をコピーしても参照がずれないようにします。

ここでは、各行が同じ製品リストを検索する必要があり、毎回検索値だけが異なるため、重要になります。

XLOOKUP()で完全一致と近似一致を使う方法

既定では、XLOOKUP()は完全一致を探します。見つからない場合はエラーを返しますが、先ほど説明したif_not_found 引数を設定していれば別です。

ただしXLOOKUP()は、match_mode引数を使って最も近い一致も見つけられます。

完全一致（デフォルト）

追加で何かを入力する必要はありません。ここまで作ってきた数式はすべて、XLOOKUP()の既定動作である完全一致を使っています。

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6)

ExcelはA2の値と完全に一致するものを探し、検索範囲のどこかでその値が見つかったときだけ結果を返します。

明示的に書きたい場合は、第4引数として0を追加します。

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6, , 0)

XLOOKUP()は完全一致を返す。画像：著者

0の前の空白に注目してください。ここではif_not_foundを空欄のまま飛ばして、match_modeに進むためのスペースです。多くの場合、この引数自体を省略して、Excelの既定の完全一致に任せれば十分です。

近似一致

今度は、データにすべての可能な検索値があるわけではないケースを考えましょう。例えば、売上金額の範囲に応じて歩合率が変わるコミッション表では、1ドルごとの行はなく、各範囲の開始値だけが並びます。

このようなときに近似一致が役立ちます。

match_modeを-1に設定すると、XLOOKUP()は検索値以下で最大の値を見つけます。

=XLOOKUP(C2, $A$2:$A$5, $B$2:$B$5, , -1)

例えば、検索範囲に$0、$5,000、$10,000というしきい値があり、それぞれに異なる歩合率が紐づいているとします。

売上$7,500の担当者は、どの行にも完全一致しません。match_modeを-1にすると、XLOOKUP()は$7,500を超えない最大のしきい値$5,000を見つけ、その行の歩合率を返します。

match_modeを1にすると、ロジックは逆になります。検索値以上で最小の値を見つけます。重量区分の送料のように、次の上位ティアに切り上げるケースで便利です。

XLOOKUP()は近似一致を返す。画像：著者

注意：近似一致は、検索範囲が-1なら昇順、1なら降順で並んでいることを前提とします。並び順が正しくないと、XLOOKUP()はエラーを出さずに誤った結果を返すことがあります。必ず、近似一致を使う前に並び順を確認してください。

XLOOKUP()で左側の値を検索する方法

VLOOKUP()は範囲の最左列しか検索できず、その右側の列から値を返します。逆方向にはできません。第3引数は範囲ではなく、左端から数えた列番号です。

「左に1列戻って」と指示する方法がないため、必要な値が検索列の左側にある場合、データの並び替えや補助列の追加で対応せざるを得ません。

XLOOKUP()は、検索範囲と返す範囲を別々に指定できるため、この制約がありません。

製品リストでは、Product IDが列A、Product Nameが列Bにあります。

製品名からIDを見つけたい、つまりVLOOKUP()の逆をやりたいとします。

C9に「Bluetooth Speaker」（一覧に存在する名前）を入力し、E2に次を入力します。

=XLOOKUP(C9, $B$2:$B$6, $A$2:$A$6)

XLOOKUP()は列Bで"Bluetooth Speaker"を探し、5行目で見つけ、列Aから"P104"を返します。列Aが列Bの左側にあっても問題ありません。

XLOOKUP()はVLOOKUP()と異なり、左側の値も検索できる。画像：著者

XLOOKUP()で複数列を返す方法

XLOOKUP()はダイナミック配列機能を使い、1つの数式で複数列を同時に返せます。方法は、return_arrayに単一列ではなく範囲を指定するだけ。すると、XLOOKUP()は一致する行全体を、必要なセル数にわたってスピルさせます。

製品リストで試してみましょう。

C9に製品ID（例：「P104」）を入力します。E2に次を入力します。

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6)

ここでのreturn_arrayはB2:D6で、製品名、カテゴリ、価格の3列を含みます。Enterを押すと、XLOOKUP()は列Aで「P104」を見つけ、Bluetooth Speaker、Electronics、$45を一度に返し、E9、F9、G9にスピルします。

XLOOKUP()は1つの数式で複数列を返す。画像：著者

これがスピル範囲の意味です。E9に実際の数式があり、F9とG9は自動的に（独自の数式なしで）埋まります。

F9をクリックすると、数式バーには元の数式がグレー表示され、直接入力されたものではないことがわかります。E9のスピルが有効な間にF9へ入力しようとすると、スピルエラーが出ます。

この対処法は後述の「よくあるエラー」で取り上げます。

XLOOKUP()で欠損値を処理する方法

既定では、XLOOKUP()は一致が見つからないと#N/Aエラーを返します。if_not_found 引数を使えば、既定のエラーの代わりに任意の表示を出せます。

たとえばC9に、データに存在しないProduct ID（例：「P999」）を入力したとします。

カスタムメッセージを返す

次にG2に入力します。

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "Not Found")

「P999」は列Aに存在しないため、XLOOKUP()はエラーを出さずに「Not Found」を返します。文言は「Check Product ID」や「No Match」など、任意に変更できます。

既定のエラーの代わりにカスタムメッセージを表示。画像：著者

空白を返す

テキストなしの空セルにしたい場合もあります。そんなときは、何も入れない二重引用符を使います。

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "")

レポートでこの数式を使い、実際の値が並ぶ列に「Not Found」が点在すると見栄えが悪い、といった場合に使えます。

既定のエラーの代わりに空白セルを表示。画像：著者

本来のエラーのままにする

if_not_foundを省略すると、XLOOKUP()は#N/Aに戻ります。これは必ずしも悪いことではありません。自分用の数式で、タイポや未入力をすぐに検知したいなら、問題を隠してしまう静かな空白セルより、#N/Aが出たほうが有用です。

注意：if_not_foundが拾うのは「一致がない」場合だけです。例えばlookup_arrayとreturn_arrayのサイズが異なると、XLOOKUP()は#VALUE!エラーを返し、if_not_foundでは防げません。これについては後述の「よくあるエラー」で扱います。

XLOOKUP()の高度な機能

ここからはXLOOKUP()の高度な機能を見ていきます。

下から上に検索する

search_modeを-1に設定すると、XLOOKUP()は範囲の下から上へと検索します。重複値があり、最初の一致ではなく最新の一致（下側）を取りたい場合に便利です。

たとえば、データセットに重複するCustomer IDが3つあるとします。E2には、上から下へ検索する既定の数式を入力します。

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6)

これは表の先頭から計算され、最初に見つかった一致の結果を表示します。

下から検索したい場合は、次の数式を使います。

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, -1)

この数式では、-1が「データセットの下から検索を開始する」ことを意味します。これにより、下から最初に一致する値である340が返されます。

XLOOKUP()は下から上にデータを走査できる。画像：著者

二分探索モード

search_modeを2にすると、XLOOKUP()は1行ずつのチェックではなく二分探索に切り替わります。

動作は次のとおりです。

• まず範囲の中央の項目を見ます。

• 一致しなければ、ありえない半分を捨て、残りの中央を見ます。

• これを繰り返し、データを毎回半分に絞りながら一致を探します。

• search_modeを-2にすると、最後の項目から始める以外は同じ動作です。

E2に次を入力します。

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, 2)

この数式は代わりに200を返し、全く別の行を指します。

二分探索モードが誤った結果を返す例。画像：著者

二分探索は、lookup_arrayがソートされている場合にしか機能しないためです。Customer IDの列がC100、C101、C100、C102、C100のように未ソートだと、二分探索は誤った半分を捨ててしまい、誤った一致を返します。

では、データが実際にソートされているとどうなるか見てみましょう。

同じログをCustomer IDの昇順（C100が先頭に3つ並ぶ）でソートし、同じ数式を再実行します。

二分探索モードはソート済みデータで機能する。画像：著者

今度はE2が120を返し、通常の検索と一致します。これが違いです。二分探索は安全に除外できるデータだけを飛ばしますが、それが成り立つのは、最初にデータがソートされている場合だけです。

XLOOKUP()の入れ子

検索結果を、固定の範囲ではなく次の検索に渡す必要がある場合、XLOOKUP()を入れ子にします。

たとえば、価格を見つけたいが、どの列から価格を取るかは別の検索（顧客の価格帯など）に依存する、といったケースです。

=XLOOKUP(C9, $A$2:$A$6, XLOOKUP(D9, $F$2:$F$4, $G$2:$I$4))

この数式では、内側のXLOOKUP()がまずD9に基づいて参照すべき列範囲を決め、その結果を外側のXLOOKUP()がreturn_arrayとして利用します。

入れ子のXLOOKUP()。画像：著者

XLOOKUP() と VLOOKUP() の比較

XLOOKUP()の動作を詳しく見てきました。ここではVLOOKUP()との比較です。

注意点は次の2つです。

• VLOOKUP()は既定で近似一致です。第4引数を忘れてもエラーは出ず、静かに誤った値を返します。

• VLOOKUP()のシートで列を挿入すると、数式がこっそり誤った値を返し始めます。XLOOKUP()は列数を数えるのではなく範囲を直接参照するため、これを回避できます。

とはいえ、VLOOKUP()が時代遅れになったわけではありません。単純で固定的なシートなら今でも問題なく使えます。ただし、これから新しく作るものでは、XLOOKUP()を既定にするほうが安全です。

XLOOKUP() と INDEX()・MATCH() の比較

XLOOKUP()が登場する前は、INDEX()とMATCH()の組み合わせがVLOOKUP()の制約を回避する標準的な方法でした。2つの関数を使います。

• MATCH()は検索値の位置を見つけます。

• INDEX()はその位置を使って、指定した場所から結果を返します。左でも右でも構いません。

=INDEX(D2:D6, MATCH(C9, A2:A6, 0))

この記事で使ってきたXLOOKUP()版と比べてみましょう。

=XLOOKUP(C9, A2:A6, D2:D6)

XLOOKUP()は、前述のとおりMicrosoft 365とExcel 2021以降でのみ動作します。ブックをExcel 2019や2016で開く必要がある場合や、古いバージョンのユーザーと共有するなら、INDEX()とMATCH()は引き続きどこでも動作します。

小さな補足もあります。

INDEX()とMATCH()は「位置を見つける」と「値を返す」を分離できるため、複雑な入れ子の数式で助かる場面があります。頻度は高くないので、多くの読者には不要でしょう。

上の2つの場合を除けば、新しいブックではXLOOKUP()を選ぶのが得策です。

XLOOKUP()でよくあるエラーと対処法

遭遇しやすい問題と、それぞれの対処法を手早く参照できるようにまとめました。

#N/A エラー

これはXLOOKUP()が一致を見つけられないときの既定の反応です。先ほども、lookup_valueとlookup_arrayが噛み合わず、列Aに突き合わせる相手がないためXLOOKUP()が何も一致させられない、という場面がありました。

対処法：lookup_value がlookup_arrayに実在し、同じ綴り・同じ書式であることを確認します。見つからないのが通常運用で想定されるなら、if_not_foundで「Not Found」や空白セルなど、有用な表示に置き換えましょう。

検索範囲の不一致

これは、lookup_arrayとreturn_arrayの整合が取れていない（行や列がずれている、サイズが異なる）場合に起きます。

ExcelやSheetsのバージョンによって、#VALUE!や、場合によっては#N/A（ただし別メッセージで真の問題を説明）として現れることがあります。#N/Aでも「一致がない」と早合点しないように注意してください。

対処法：lookup_array とreturn_arrayが同じ行数（あるいは列数）をカバーしているか確認します。lookup_arrayがA2:A5なら、return_arrayも2行目から5行目をまたぐ必要があります。4〜5行目だけ、などの不一致は不可です。

データ型の不一致

VLOOKUP()は数値を期待しているのにテキストを受け取るとエラーになることがあります。XLOOKUP()でも同様の問題が起こりえます。画面上は同じに見えても、lookup_value が数値で、lookup_array側が同じ値をテキストとして保持している（または逆）と、一致は見つかりません。

対処法：lookup_value とlookup_arrayが同じデータ型かを確認します。テキストとして保存された数値は、通常セル内で左寄せ表示になります。lookup_valueにVALUE()をかけてテキストを数値に変換するか、逆にTEXT()で数値をテキストにするなど、ずれの方向に応じて調整します。

スピルエラー

これは、return_arrayが複数列にわたるのに、XLOOKUP()が必要とするセルが空いていない場合に起こります。数式セルの隣に結果をスピルするスペースが必要で、既に何かがあると数式全体が失敗します。

対処法：結果が入るセルを空けてから数式を入力します。どうしてもそのセルを使いたいなら、XLOOKUP()の数式を空きスペースのある別の場所へ移動してください。

まとめ

これで、XLOOKUP()を使って単一値、複数列、欠損データの処理、さらには2つの範囲にまたがる入れ子の検索まで扱えるようになりました。かなりの範囲をカバーしました。

今後は、新しいブックではXLOOKUP()を既定にしましょう。VLOOKUP()より柔軟で、INDEX/MATCH()より読みやすく、すべての数式をIFERROR()で包む手間も省けます。

さらに深く学ぶなら、INDEX()とMATCH()自体も習得する価値があります。