M365Con 2027 – 無料オンラインカンファレンス · 11–22 Jan 2027 · 120 sessions→ m365con.net

ブログ · Mirko Peters

Excel の XLOOKUP: 構文、使用例、VLOOKUP との違い

By · · Updated

Excel の XLOOKUP を解説します。構文、各引数、完全一致・ワイルドカード一致・最後の一致の例、複数列の検索、VLOOKUP との違いを扱います。

Automatically translated with AI from the original post. Read the English original

Excel の XLOOKUP は、ある範囲から値を探し、別の範囲から対応する項目を返します。書式は =XLOOKUP(lookup_value, lookup_array, return_array) です。社員 ID を入力し、ID の列を指定して、名前の列を指定すれば、名前が返されます。この記事は、癖で今も VLOOKUP を書いている Excel ユーザーや、同僚のファイルで初めて XLOOKUP を見た方を対象としています。構文、6 つの引数、実務でよくある場面の使用例、VLOOKUP との比較、発生しやすいエラーの一覧を紹介します。

重要なポイント

  • XLOOKUP は、検索値、検索範囲、戻り値の範囲の 3 つを受け取ります。書式は =XLOOKUP(lookup_value, lookup_array, return_array) です。最初に一致した値を見つけ、戻り値の範囲から対応する項目を返します。
  • 検索列の左側を検索でき、既定で完全一致になります。どちらも VLOOKUP にはできないこと、または既定では行わないことです。
  • 4 番目の引数 if_not_found は、#N/A エラーの代わりに指定した文字列を表示します。この引数を省略すると、一致しない検索の結果は #N/A になります。
  • match_mode の 2 はワイルドカードを有効にします。-1 と 1 は、次に小さい項目または次に大きい項目を検索します。search_mode の -1 は、最後の項目から最初の項目へ向かって検索します。
  • Microsoft のページでは、XLOOKUP は Microsoft 365、2021、2024 で使えると記載されています。一方、Excel 2016 と 2019 に関する記述は食い違っているため、共有ファイルで依存する前に必ずテストしてください。

以下の構文とオプションは、Microsoft の XLOOKUP 関数のページに基づいています。そのページに載っていないパターンについては、本文でその旨を記載しています。XLOOKUP は Excel の関数であり、VLOOKUP のヘルプページを開くと Microsoft 自身も、より良い選択肢として XLOOKUP を案内しています。

構文と 6 つの引数

完全な数式は次のとおりです。

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

最初の 3 つの引数は必須です。残りの 3 つは省略可能です。

  • lookup_value は検索する値です。たとえばセル D2 の ID などです。省略した場合、XLOOKUP は検索配列内で見つかった空白セルを返します。
  • lookup_array は検索対象の範囲または配列です。たとえば A2:A100 などです。
  • return_array は値を返す範囲または配列です。たとえば B2:B100 などです。
  • if_not_found は、有効な一致がない場合に表示する文字列です。省略すると #N/A が表示されます。
  • match_mode は一致の方法を指定します。0 は完全一致で、既定値です。-1 は完全一致、または一致するものがなければ次に小さい項目を返します。1 は完全一致、または次に大きい項目を返します。2 はワイルドカード一致で、*、?、~ に特別な意味があります。
  • search_mode は検索の方向を指定します。1 は最初の項目から最後の項目へ検索し、既定値です。-1 は最後から最初へ検索します。2 と -2 は、昇順または降順に並んだデータに対するバイナリ検索です。Microsoft は、並べ替えられていないデータをバイナリ検索モードで使うと、無効な結果になると警告しています。

検索配列と戻り値の配列は、同じサイズにそろえてください。検索配列が 2 行目から 100 行目までを対象にしているなら、戻り値の配列も 2 行目から 100 行目までにします。Microsoft's のページではこれをルールとして明記していませんが、範囲がずれていることは、検索結果が誤った行になったりエラーになったりする典型的な原因です。

基本的な検索と「見つからない場合」の引数

社員 ID が A2:A100 に、名前が B2:B100 にあるとします。D2 に検索したい ID を入力します。

  1. 結果を表示したいセル(たとえば E2)をクリックします。
  2. =XLOOKUP(D2,A2:A100,B2:B100) と入力します。
  3. Enter キーを押します。

ID に対応する名前が表示されます。XLOOKUP は、最初に見つかった一致の値を返します。ID が存在しない場合は #N/A が表示されます。

より分かりやすい表示にするには、4 番目の引数を追加します。

=XLOOKUP(D2,A2:A100,B2:B100,"No record found")

これで、一覧にない ID の場合は「No record found」と表示されます。他の人が読むシートでは、このように設定しておくと便利です。ただし注意点があります。このようなメッセージは、実際の問題を隠してしまうことがあります。すべての行が「No record found」になる場合は、データではなく範囲の指定に問題がある可能性が高いため、まず既知の ID で検索をテストしてください。

左方向への検索

VLOOKUP では、検索列より左側の列から値を返すことはできません。XLOOKUP では、検索範囲と戻り値の範囲が独立しているため、それが可能です。名前が C 列にあり、取得したい ID が A 列にある場合は、次のようにします。

=XLOOKUP(F2,C2:C100,A2:A100)

Microsoft's のページにも、検索列は戻り値の列の左右どちらにあってもよいと明記されています。

一致モード: ワイルドカードと近似値

ワイルドカードによる一致

ワイルドカードを使うには、match_mode を 2 に設定します。アスタリスクは任意の数の文字、疑問符は任意の 1 文字を表します。チルダを使うと、アスタリスクや疑問符そのものを検索できます。

=XLOOKUP("Jan*",B2:B100,A2:A100,"Not found",2)

これは「Jan」で始まる最初の項目を返します。2 を省略すると、Excel は「Jan*」という文字列をそのまま探し、見つからない場合のメッセージを返します。これはワイルドカードで最もよくある間違いです。XLOOKUP は最初の一致しか返さないため、ワイルドカードはできるだけ具体的にしてください。条件が緩いと、意図しないものに一致する可能性があります。

次に小さい、または次に大きい項目

モード -1 と 1 は、税率、手数料の区分、価格の区切りなど、段階的な値に使います。しきい値を A2:A6 に、料率を B2:B6 に入力し、E2 の所得額で検索する場合は次のようにします。

=XLOOKUP(E2,$A$2:$A$6,$B$2:$B$6,,-1)

-1 を指定すると、Excel は完全一致の値を返します。完全一致がない場合は、次に小さいしきい値の値を返します。-1 の前の空の引数は、「見つからない場合」の引数を省略するためのものです。Microsoft の近似一致の例では、1 を使って次に大きい所得額の税率を返しています。表の構成に合わせて方向を選んでください。従来の近似一致の VLOOKUP と違い、これらのモードではしきい値を並べ替える必要はありません。並べ替えが必要なのは二分探索モードだけです。

末尾から検索する: 最後の一致

同じ値が複数回出現する場合、XLOOKUP は最初に見つかったものを返します。最後の一致を取得するには、search_mode を -1 に設定して、末尾から検索します。

=XLOOKUP(D2,A2:A100,C2:C100,"Not found",0,-1)

シートの注文が日付順に並んでいる場合、この数式は顧客の最新の注文を返します。0 は完全一致を維持するための指定です。「最新価格」「最終連絡日」など、VLOOKUP では扱いにくかった用途に使える書き方です。

複数の列を一度に返す

Microsoft's のページによると、VLOOKUP と違い、XLOOKUP は複数の列を 1 つの配列として返せます。戻り値の範囲を広げます。

=XLOOKUP(D2,A2:A100,B2:D100)

この数式は、B 列で一致した値をセルに表示し、C 列と D 列の値を右側のセルにスピル(展開)します。そのためのスペースを空けておいてください。右側のセルに既にデータがある場合、Excel は上書きせずにスピルエラーを表示します。Microsoft's の例では、1 つの数式で社員名と部署名を返しています。

双方向の検索

Microsoft's では、2 つの XLOOKUP 関数を入れ子にして、行と列が交差する位置の値を検索する方法が紹介されています。そのページにある書き方は次のとおりです。

=XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17))

内側の関数は、C3 のヘッダーと一致する列を選びます。外側の関数は行見出しから D2 を探し、その列の値を返します。Microsoft はこれを INDEX と MATCH の組み合わせに相当するものとして説明しています。製品を縦に、月を横に並べたレポートであれば、より複雑な数式の置き換えとして使えます。

複数の条件

XLOOKUP は検索値を 1 つだけ受け取り、Microsoft のページには複数条件の例はありません。よく使われる方法は、Excel に 2 つの条件を同時に判定させることです。カテゴリが A 列、ステータスが B 列、価格が C 列にあるとします。F2 のカテゴリと G2 のステータスに対応する価格を探すには、次のようにします。

=XLOOKUP(1,(A2:A100=F2)*(B2:B100=G2),C2:C100,"Not found")

各比較式は TRUE または FALSE を返します。それらを掛け合わせると、両方が一致する行でのみ 1 になり、XLOOKUP は最初の 1 を探します。現在の Microsoft 365 のバージョンで動作するパターンですが、Microsoft の関数ページには載っていないため、小さなサンプルで必ずテストしてください。2 つのフィールドを結合する補助列を使う方法も、古くからある代替手段です。=A2&"|"&B2 のように値の間に区切り文字を入れ、結合したテキストで検索します。

XLOOKUP と VLOOKUP の比較

Microsoft の VLOOKUP のページでは、構文は VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) と示されており、検索値は範囲の最初の列になければならないと説明されています。range_lookup を省略すると、VLOOKUP は近似一致で検索します。これは最初の列が並べ替えられていることを前提とします。完全一致にするには FALSE を指定します。同じページでは XLOOKUP の使用も勧めており、XLOOKUP はどの方向にも検索でき、既定で完全一致を返すと説明しています。

A 列に ID、B 列に名前がある場合に、両方の関数で同じ処理を行う例です。

  • =VLOOKUP(D2,A2:B100,2,FALSE)
  • =XLOOKUP(D2,A2:A100,B2:B100)

日常の利用で重要になる違いは次のとおりです。

  • VLOOKUP は結果を列番号で参照するため、テーブルに列を挿入すると、その番号が指す意味が変わります。XLOOKUP は戻り値の範囲を直接参照します。
  • VLOOKUP は左側を検索できません。XLOOKUP はできます。
  • VLOOKUP は既定で近似一致になります。XLOOKUP は既定で完全一致になります。
  • XLOOKUP には、見つからない場合の引数と検索方向の指定が組み込まれています。

古いブックをすべて書き直す必要はありません。VLOOKUP が正しく動作しているなら、そのままにしておいてください。新しい数式には XLOOKUP を使い、左方向の検索、最後の一致の取得、複数の戻り列が必要な場合に活用するとよいでしょう。

XLOOKUP に対応している Excel のバージョン

ここは注意が必要です。Microsoft のページでは、XLOOKUP は Windows と Mac の Excel for Microsoft 365、Excel 2024、Excel 2021、iPad・iPhone・Android のアプリで利用できると記載されています。一方、同じページの注記では「Excel 2016 および 2019 では利用できないが、新しいバージョンで作成されたブックには表示される場合がある」とされており、適用対象の行には 2016 と 2019 も挙げられています。記述が矛盾しているため、Excel 2016 と 2019 を安全なものとして扱わないでください。また、Excel for the web もこのページには記載されていません。

古いバージョンを使っている相手にブックを送ると、本来は値が表示されるはずの場所に、数式のエラーが表示されることがあります。相手が使用しているバージョンを確認し、多くの人に配布するファイルは、現在も使われている最も古いバージョンでテストしてください。テストできない場合は、そのファイルでは従来の検索関数を使ってください。

よくあるミスとエラー

  • ワイルドカードを使うときに match_mode の 2 を指定し忘れる。その場合、検索は文字どおりの文字を探してしまいます。
  • 並べ替えられていないデータに対して、二分探索の search_mode(2 または -2)を使う。Microsoft によると、その結果は無効です。
  • 検索範囲と戻り値の範囲のサイズが異なる。両方を同じ長さにしてください。
  • 目に見えない空白や、テキストとして保存された数値。「100」と 100 は別の値として扱われるため、検索は失敗し、#N/A が表示されます。TRIM で データをクリーニングするか、数値に変換してください。
  • わかりやすい「見つかりません」というメッセージで問題を隠してしまう。結果を信頼する前に、既知の値を 1 つテストしてください。
  • 結果をスピルさせる必要があるセルに戻り値の配列を指定し、そのセルに別のデータを入れたままにしている。
  • 非常に大きなシートで列全体の参照を使う。範囲を限定するか、Excel のテーブルを使い、Excel が処理するセル数を減らしてください。

関連して確認しておきたいトピック

  • XLOOKUP と INDEX・MATCH の比較。どちらも左方向の検索に対応しており、INDEX と MATCH の組み合わせは古いバージョンの Excel でも使えます。Excel 2016 や 2019 を使う相手とファイルを共有する場合は、こちらも確認しておくとよいでしょう。
  • 複数の結果。 XLOOKUP が返すのは最初に一致した値だけです。すべての一致を取得したい場合は、代わりに FILTER 関数を確認してください。
  • Excel テーブルでの XLOOKUP。 構造化参照を使うと、範囲がデータの増加に合わせて広がり、数式も読みやすくなります。
  • Power Query のデータ。 データをテーブルに読み込んだ後は、XLOOKUP で他のテーブルと同じように検索できます。先にクエリを更新し、後で結果を確認してください。
  • Excel for the web。 Microsoft の XLOOKUP のページには記載がありません。ファイルを共有する前に、自分の環境で同僚にも関数が機能するかを確認してください。

次のステップ

現在のブックから VLOOKUP を 1 つ選び、XLOOKUP に書き換えてみてください。見つからない場合のメッセージも追加し、既知の ID をいくつか使って 2 つの結果を比較します。両方が一致すれば、Excel で今後新しく XLOOKUP を使うときに再利用できるパターンが得られます。

データソース

Entities in this article

Microsoft Excel Excel for the web

Related articles

RSS