XLOOKUP関数の応用テクニック｜複数条件・逆方向検索

XLOOKUP関数は基本的な検索だけでなく、複数条件での検索や逆方向検索、ワイルドカードを使った部分一致検索など、高度な使い方が可能です。VLOOKUPでは実現が難しかった処理をシンプルな数式で記述できるため、実務での活用範囲が大きく広がります。この記事では、XLOOKUP関数の応用テクニックを実践的な例とともに解説します。

XLOOKUP関数の引数を確認する

応用テクニックに入る前に、XLOOKUP関数の全引数を確認しておきましょう。

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

一致モードの選択肢

値	動作
0（既定）	完全一致
-1	完全一致、または次に小さい値
1	完全一致、または次に大きい値
2	ワイルドカード一致

検索モードの選択肢

値	動作
1（既定）	先頭から末尾へ検索
-1	末尾から先頭へ検索（逆方向検索）
2	昇順バイナリ検索
-2	降順バイナリ検索

これらの引数を使いこなすことで、さまざまな検索パターンに対応できます。

複数条件での検索

XLOOKUPでは、検索値と検索範囲を文字列結合することで、複数の条件を同時に指定した検索が可能です。

部署と役職で社員を検索する

以下のような社員データがあるとします。

A列（部署）	B列（役職）	C列（氏名）	D列（内線）
営業部	部長	田中太郎	1001
営業部	課長	鈴木花子	1002
経理部	部長	佐藤一郎	2001
経理部	課長	山田美咲	2002
総務部	部長	高橋健一	3001

営業部の課長の氏名を取得する数式です。

=XLOOKUP("営業部" & "課長", A2:A6 & B2:B6, C2:C6, "該当なし")

検索値を「営業部課長」とし、検索範囲もA列とB列を結合した値と照合します。結果は「鈴木花子」です。

セル参照で条件を指定する

検索条件をセルから取得する場合の書き方です。F1に部署名、G1に役職名が入力されているとします。

=XLOOKUP(F1 & G1, A2:A6 & B2:B6, C2:C6, "該当なし")

3つ以上の条件

同じ要領で3つ以上の条件にも対応できます。

=XLOOKUP(F1 & G1 & H1, A2:A6 & B2:B6 & C2:C6, D2:D6, "該当なし")

条件が増えても数式の構造は変わりません。結合する列を増やすだけで対応できるのがXLOOKUPの利点です。

逆方向検索（末尾からの検索）

検索モードに-1を指定すると、範囲の末尾から先頭に向かって検索します。同じ検索値が複数ある場合に、最後のデータを取得したいときに使います。

A列（顧客名）	B列（取引日）	C列（金額）
ABC商事	2026/1/15	500000
DEF工業	2026/2/01	320000
ABC商事	2026/2/20	750000
ABC商事	2026/3/10	680000
DEF工業	2026/3/15	410000

ワイルドカード検索

一致モードに2を指定すると、ワイルドカードを使った部分一致検索が可能になります。

使用できるワイルドカード

記号	意味	例
*	任意の長さの文字列	”電子”
?	任意の1文字	”A00?”
~	エスケープ文字	”~*“

商品名の部分一致検索

商品マスタから、商品名に特定のキーワードを含む商品を検索する例です。

A列（商品コード）	B列（商品名）	C列（単価）
E001	ワイヤレスマウス	3500
E002	有線マウス	1200
E003	ワイヤレスキーボード	5800
E004	USBハブ	2200

「ワイヤレス」を含む最初の商品コードを取得する数式です。

=XLOOKUP("*ワイヤレス*", B2:B5, A2:A5, "該当なし", 2)

結果は「E001」（ワイヤレスマウス）です。一致モード2を指定しているため、ワイルドカードが有効になります。

前方一致検索

商品コードの先頭が「E00」で始まるデータを検索する場合です。

=XLOOKUP("E00*", A2:A5, B2:B5, "該当なし", 2)

1文字のワイルドカード

商品コードの末尾1文字だけが異なるデータを検索する場合です。

=XLOOKUP("E00?", A2:A5, B2:B5, "該当なし", 2)

「E00」の後に任意の1文字が続くコードに一致します。

バイナリ検索モード

検索モードに2（昇順）または-2（降順）を指定すると、バイナリ検索（二分探索）が実行されます。

バイナリ検索の特徴

特徴	通常の検索	バイナリ検索
検索速度	データ量に比例して遅くなる	データ量が増えても高速
データの前提条件	並び順不問	昇順または降順に並んでいる必要がある
適したデータ量	小〜中規模	大規模（数万行以上）

昇順データでのバイナリ検索

社員番号が昇順に並んでいる大規模な社員名簿（数万行）から検索する例です。

=XLOOKUP(F1, A2:A50000, B2:B50000, "該当なし", 0, 2)

検索モード2により、昇順バイナリ検索が実行されます。通常の検索では先頭から1行ずつ確認するため時間がかかりますが、バイナリ検索ではデータを半分ずつ絞り込むため、大幅に高速化できます。

降順データでのバイナリ検索

売上金額が降順（大きい順）に並んでいるランキングデータから検索する場合です。

=XLOOKUP(F1, B2:B1000, A2:A1000, "該当なし", 0, -2)

検索モード-2で降順バイナリ検索を実行します。

バイナリ検索の注意点

データが正しく並んでいないと、誤った結果を返す可能性があります
並び順が保証されていない場合は、通常の検索モード（1または-1）を使用してください
数百行程度のデータでは、通常の検索でも十分高速なため、バイナリ検索のメリットは限定的です

複数列の結果を一括取得する

XLOOKUP関数は、戻り範囲に複数の列を指定することで、検索結果を複数列に一括で展開できます。

商品情報をまとめて取得する

商品コードから商品名、カテゴリ、単価をまとめて取得する例です。

A列	B列	C列	D列
商品コード	商品名	カテゴリ	単価
P001	コピー用紙	文具	3500
P002	トナー	OA	12000
P003	ボールペン	文具	150

=XLOOKUP("P002", A2:A4, B2:D4, "該当なし")

戻り範囲をB2:D4（3列）に指定しているため、結果が3つのセルにスピル（自動展開）されます。1つの数式で商品名、カテゴリ、単価がすべて取得できます。

スピルを活用した一覧表の作成

複数の検索値に対して一括で結果を取得する場合は、検索値も配列で指定できます。

=XLOOKUP(F2:F5, A2:A4, B2:D4, "該当なし")

F2:F5に入力された4つの商品コードに対して、それぞれの商品情報が一括で展開されます。

近似一致の活用

一致モード-1（次に小さい値）や1（次に大きい値）を使うと、料金表やランク判定に活用できます。

送料テーブルの参照

購入金額に応じた送料を求める例です。

A列（金額下限）	B列（送料）
0	800
3000	500
5000	300
10000	0

購入金額が4500円のとき、該当する送料を取得する数式です。

=XLOOKUP(4500, A2:A5, B2:B5, , -1)

一致モード-1により、4500以下の最大値である3000の行が選ばれ、送料500が返されます。VLOOKUPのTRUEモードと同じ動作ですが、XLOOKUPでは意図が明確に表現できます。

評価ランクの判定

テストの点数に応じたランクを判定する例です。

A列（基準点）	B列（ランク）
0	D
60	C
70	B
85	A
95	S

=XLOOKUP(78, A2:A6, B2:B6, , -1)

78以下の最大値は70なので、ランクBが返されます。

まとめ

XLOOKUP関数の応用テクニックを活用すれば、複雑な検索処理もシンプルな数式で実現できます。以下のポイントを押さえておきましょう。

複数条件検索は、検索値と検索範囲の文字列結合で対応できる
検索モード-1で末尾から検索し、最新のデータを取得できる
一致モード2でワイルドカードを使った部分一致検索が可能
大量データにはバイナリ検索モード（2または-2）で高速化できる
戻り範囲に複数列を指定すると、結果がスピルで一括展開される

XLOOKUPはMicrosoft 365およびExcel 2021以降で利用可能です。これらの応用テクニックを使いこなすことで、VLOOKUPとINDEX+MATCHの組み合わせで書いていた複雑な数式を大幅に簡素化できます。