Excel XLOOKUP関数｜VLOOKUP弱点解消！複数条件

XLOOKUP関数とは？VLOOKUPの弱点を解消！複数条件・柔軟な検索も簡単に
Excelでデータ検索といえば「VLOOKUP」が有名ですが、実はその進化版として「XLOOKUP」関数が登場しています。
VLOOKUPの弱点であった「検索方向が固定されている」「列番号が変わると式が壊れる」などの不便を解消し、より柔軟で分かりやすいデータ検索が可能になりました。

本記事では、XLOOKUPの基本構文から、単一条件・複数条件の検索方法まで実例付きで解説します。

Contents

1 1. XLOOKUP関数の基本構文
- 1.1 各引数の意味
2 2. 商品情報をXLOOKUPで一括取得する方法
- 2.1 商品名から「商品番号」「単価」を一度に求める実例
3 3. 複数条件でのXLOOKUP実例
- 3.1 「商品名」と「サイズ」から「在庫数」を求める実例
  - 3.1.1 A3をキーにE列、B3をキーにF列を検索し両方一致した行のF列とG列を求める数式
4 4. 一致するデータを下から上に検索して求める方法
- 4.1 例：「顧客ID」から最新の「プラン名」と「契約日」を求める
  - 4.1.1 E列を下から上へ検索し、最初に一致したF列とG列の値を求める数式
5 5. 近似一致の使い方
- 5.1 例：金額によって送料を求める
  - 5.1.1 「近似一致」を使用した数式
6 6. まとめ
7 7.関連記事

1. XLOOKUP関数の基本構文

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

各引数の意味

検索値：探したい値（例：「商品A」）
検索範囲：検索対象の範囲（例：商品名の列）
返す範囲：検索が一致した場合に返す列
[見つからない場合]（省略可）：該当がない場合に表示する値（デフォルトは#N/A）
[一致モード]（省略可）：完全一致・近似一致などの設定　　　　　　
• 0: 完全一致（初期値）
• -1: 検索値以下の最大値
• 1: 検索値以上の最小値
• 2: ワイルドカード一致（例: * や ? を含む）
[検索方向]（省略可）：上から下（デフォルト）か下から上かを設定
• 1: 上から下へ（初期値）
• -1: 下から上へ
• 2: バイナリ検索昇順（高速）
• -2: バイナリ検索降順

2. 商品情報をXLOOKUPで一括取得する方法

商品名から「商品番号」「単価」を一度に求める実例

A3をキーにE列を検索し一致したF列とG列を求める数式

=XLOOKUP($A3,$E3:$E7,$F3:$G7,"該当なし")

スビル機能で、「単価」(C3)に自動設定された数式

見つからない場合の実例

見つからない場合は 4番目の引数でメッセージを設定可能です。(例では、「該当なし」)
4番目の引数を省略すると、VLOOKUPと同様に「#N/A エラー」を返します。

3. 複数条件でのXLOOKUP実例

XLOOKUP関数で複数条件を指定するには、検索範囲で複数の条件を掛け合わせることで実現できます。

「商品名」と「サイズ」から「在庫数」を求める実例

A3をキーにE列、B3をキーにF列を検索し両方一致した行のF列とG列を求める数式

=XLOOKUP(1,($E3:$E6=$A$3)*(F3:F6=$B$3),G3:G6, "該当なし")

<説明>

=XLOOKUP(1,($E3:$E6=$A$3)*($F3:$F6=$B$3),$G3:$G6, “該当なし”)
この数式では、以下のように処理されます。
①($E3:$E6=$A$3)…商品名が一致したらTRUE（1）、不一致はFALSE（0）
②($F3:$F6=$B$3)…サイズが一致したらTRUE（1）、不一致はFALSE（0）
③ 上記2つの結果を乗算(*)します。乗算の結果、両方の条件がTRUEの場合のみ行がTRUE（1）となり、それ以外はFALSE（0）となります。
④1番目の引数(検索値) に1 を指定することで、③の結果がTRUE（1）の行(商品名とサイズが共に一致)のG列（在庫数）を返す

4. 一致するデータを下から上に検索して求める方法

6番目の引数[検索方向]を、「-1」( 下から上へ)にすることで、検索範囲の一番下から上に検索が実施されます。これにより、一致するデータが複数ある場合一番下にあるデータを求めることができます。

例：「顧客ID」から最新の「プラン名」と「契約日」を求める

E列を下から上へ検索し、最初に一致したF列とG列の値を求める数式

=XLOOKUP($A$3, $E$3:$E$7, $F$3:$G$7, "", 0, -1)

5. 近似一致の使い方

例：金額によって送料を求める

「近似一致」を使用した数式

=XLOOKUP($A$3,$E$3:$E$5,$F$3:$F$5,"該当なし",-1)

5番目の引数(一致モード)を、省略時の「完全一致」ではなく、「近似一致」指定をすることで、便利な使い方ができます。
以下の例で、近似一致の値に(-1: 検索値以下の最大値)を設定することで、別な関数で上限下限の範囲判定をするようなことをしなくても、判断ができます。
具体例で、4000円の指定をした場合、それ以下の最大値3000円の送料が求まります。