g716｜Excel XLOOKUP関数：入れ子で2次元データ検索を自由自在に！INDEX/MATCHを超える柔軟性

はじめに

Excelで2次元の表（行と列の見出しを持つ表）からデータを検索する場合、従来のINDEX関数とMATCH関数の組み合わせがよく使われていました。しかし、XLOOKUP関数を入れ子にして使用することで、よりシンプルかつ直感的に同じ処理を行うことができます。この記事では、XLOOKUPの入れ子に焦点を当て、その利点や具体的な使用方法を解説します。

XLOOKUP関数とは？（復習）

XLOOKUP関数は、指定された範囲または配列で値を検索し、最初に見つかった一致に対応する項目を返す関数です。

基本的な構文（復習）

Excel

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

各引数の意味は以下の通りです。

• 検索値（必須）: 検索する値を指定します。
• 検索範囲（必須）: 検索を行う範囲を指定します。
• 戻り範囲（必須）: 結果として返す値が含まれる範囲を指定します。
• 見つからない場合（省略可能）: 検索値が見つからない場合に返す値を指定します。
• 一致モード（省略可能）:
  • 0（既定値）：完全一致。
  • -1：完全一致。見つからない場合は、次に小さい項目を返します。
  • 1：完全一致。見つからない場合は、次に大きい項目を返します。
  • 2：ワイルドカード文字（*、?、~）を使用した部分一致。
• 検索モード（省略可能）:
  • 1（既定値）：先頭から末尾へ検索。
  • -1：末尾から先頭へ検索。
  • 2：昇順で並べ替えられた範囲を使用したバイナリ検索。
  • -2：降順で並べ替えられた範囲を使用したバイナリ検索。

＜Excelサンプルデータダウンロード＞
Excel XLOOKUP関数：表の縦横の見出しから２次元データ検索を自由自在！INDEX/MATCHを超える柔軟性

XLOOKUP関数の入れ子とは？

XLOOKUP関数の入れ子とは、あるXLOOKUP関数の戻り範囲に別のXLOOKUP関数を使用することです。これにより、複数の条件に基づいてデータを検索することができます。

画像例の解説

画像では、スキーリフト代金表（B7:D10）から、券種（行見出し）と年齢区分（列見出し）に基づいて料金を検索しています。セルB4に入力されている数式は以下の通りです。

Excel

=XLOOKUP(B3,B7:D7,XLOOKUP(B2,A8:A10,B8:D10))

この数式を分解して解説します。

1. 外側のXLOOKUP:
   • B3: 検索値（券種）が入力されています。例では「2デイ」です。
   • B7:D7: 検索範囲（券種の行見出し）を指定しています。
   • XLOOKUP(B2,A8:A10,B8:D10): 内側のXLOOKUP関数が戻り範囲として使用されています。
2. 内側のXLOOKUP:
   • B2: 検索値（年齢区分）が入力されています。例では「小学生」です。
   • A8:A10: 検索範囲（年齢区分の列見出し）を指定しています。
   • B8:D10: 戻り範囲（チケット料金のデータ範囲全体）を指定しています。

数式の動作

1. 内側のXLOOKUP関数が最初に実行されます。これは、年齢区分「小学生」に対応する料金の列（B列）を返します。
2. 外側のXLOOKUP関数は、内側のXLOOKUP関数が返した列（B列）を戻り範囲として使用し、券種「2日」に対応する料金（B9の7,500）を返します。

Excelのサンプルデータ【ダウンロード】

以下は、上記画像のExcelデータですので、ダウンロードして練習などに使用してください。

Excel-g716-1.xlsx  （ダウンロード）

XLOOKUP入れ子のメリット

• INDEX/MATCHよりもシンプル: INDEX/MATCHの組み合わせに比べて、数式が簡潔で理解しやすくなります。
• 柔軟な検索: 行と列の検索範囲を自由に指定できるため、データ構造に柔軟に対応できます。
• エラー処理が容易: XLOOKUPの見つからない場合引数を利用して、エラー処理を簡単に行うことができます。

XLOOKUP入れ子とINDEX/MATCHの比較

特徴	XLOOKUP入れ子	INDEX/MATCH
数式の簡潔さ	シンプル	複雑
可読性	高い	低い
柔軟性	検索範囲を自由に指定可能	柔軟性は高いが、数式が複雑になる傾向がある

まとめ

XLOOKUP関数を入れ子にして使用することで、2次元の表からデータを効率的に検索できます。INDEX/MATCHの代替として、よりシンプルで可読性の高い数式を実現できるため、積極的に活用することをお勧めします。

この解説で、XLOOKUP関数の入れ子についてより深く理解できたかと思います。この情報を活用し、Excelでのデータ分析をより効率的に行いましょう。