Excelの高度なルックアップ術をマスター：XLOOKUPとINDEX-MATCH-MATCHでVLOOKUPを超える

Excelには、データ管理やデータ分析に役立つ多彩な検索（ルックアップ）機能が備わっています。VLOOKUPは定番のデータ抽出関数ですが、「検索列が左端にある必要がある」「エラー処理が柔軟でない」といった制約があります。こうした課題を解決できるのが、XLOOKUPやINDEX-MATCH-MATCHといった高度な関数です。本記事では、実例を交えながら、VLOOKUPを超える柔軟性・制御力・効率性を実現するこれらの検索テクニックを詳しく解説します。

以下では、売上データセットを題材に、実践的な例を使って高度な検索テクニックを見ていきましょう。

XLOOKUPによる高度な検索テクニック

XLOOKUPは、単一条件から複数列にわたる検索までこなせる万能関数です。左右どちらの方向にも（縦方向・横方向とも）検索でき、見つからない場合のカスタムメッセージを指定でき、さらにデータが並べ替えられていなくても使用できます。なお、XLOOKUPはExcel 2021およびMicrosoft 365でのみ利用可能です。また、検索対象の値が変更されると、結果も自動的に更新されます。

書式：

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

• 検索値： 探したい値。
• 検索範囲： 検索対象となる範囲または配列。
• 戻り範囲： 結果として返す値が含まれる範囲または配列。
• [見つからない場合]（省略可能）： 一致する値が見つからなかった場合に返す値。
• [一致モード]（省略可能）： 一致の種類を指定します。完全一致、ワイルドカード、近似一致から選択できます。
  • 0 – 完全一致（既定）
  • 1 – 完全一致、または次に大きい値
  • -1 – 完全一致、または次に小さい値
  • 2 – ワイルドカード一致
• [検索モード]（省略可能）： 検索の方向（先頭から末尾、または末尾から先頭など）を指定します。
  • 1 – 先頭から末尾へ検索
  • -1 – 末尾から先頭へ検索
  • 2 – バイナリ検索（昇順データ）
  • -2 – バイナリ検索（降順データ）

1. 単一条件でのXLOOKUP活用

まずは売上データセットから、100ドルに最も近い売上金額を検索し、1件あたり約100ドルを支出した顧客を特定してみましょう。次の数式を入力します。

数式：

=XLOOKUP(100, G2:G71, A2:G71, "Not Found", 1)

この数式は、範囲G2:G71の中から100を検索し、対応する行のデータをA2:G71から返します。100に近い値が見つからない場合は「Not Found」と表示されます。

出力結果：

1007 ／ 2024/1/4 ／ Daniel Martinez ／ East ／ 39.99 ／ 3 ／ 119.97

2. 複数条件でのXLOOKUP活用

XLOOKUPでは、複数の条件を「&」で連結することで、より複雑な検索も実現できます。次の数式を見てみましょう。

数式：

=XLOOKUP("Melissa Lopez" & "West", C2:C71 & D2:D71, A2:G71)

この数式は、顧客名と地域を連結した文字列を作成し、同じく連結した検索配列の中から一致する行を探します。見つかった行の対応するデータを、指定した範囲から取得します。

出力結果：

1012 ／ 2024/1/6 ／ Melissa Lopez ／ West ／ 79.99 ／ 2 ／ 159.98

INDEX-MATCH-MATCHによる高度な検索テクニック

INDEX-MATCH-MATCHは、行と列の両方の条件に基づいて値を検索したい場合に使う組み合わせで、2次元のデータ表に最適です。

書式：

=INDEX(配列, MATCH(行検索値, 行検索範囲, 0), MATCH(列検索値, 列検索範囲, 0))

• 配列： 取得したい値が含まれるセル範囲。
• MATCH(行検索値, 行検索範囲, 0)： 行番号を返します。
  • 行検索値： 行の中で探す値。
  • 行検索範囲： 検索対象となる行の範囲。
  • 0 – 完全一致
• MATCH(列検索値, 列検索範囲, 0)： 列番号を返します。
  • 列検索値： 列の中で探す値。
  • 列検索範囲： 検索対象となる列の範囲。
  • 0 – 完全一致

1. 行と列を組み合わせた2次元検索

INDEX-MATCH-MATCHの数式を使って、特定の顧客の売上金額を確認してみましょう。次の数式を入力します。

=INDEX(A2:G71, MATCH("John Smith", C2:C71, 0), MATCH("Sales Amount", A1:G1, 0))

この数式は、範囲C2:C71の中から「John Smith」を、A1:G1の中から「Sales Amount」をそれぞれ検索し、両者が交差する位置の値をA2:G71から返します。

出力結果：

99.98

2. 配列数式による3次元検索への拡張

INDEX-MATCH-MATCHは通常2次元の検索に使われますが、複数のMATCH関数を組み合わせた配列数式を使えば、3次元的な検索にも拡張できます。大規模データセットや複雑な条件での照合に最適で、柔軟性と能力の面でVLOOKUPを大きく上回ります。

数式：

=INDEX(G2:G71, MATCH(1, (C2:C71="John Smith") * (D2:D71="South") * (F2:F71=4), 0))

この数式は、「顧客名（C列）＝John Smith」「地域（D列）＝South」「数量（F列）＝4」という3つの条件をすべて満たす行を検索し、対応する売上金額（G2:G71）を返します。条件同士を掛け算（AND条件相当）で組み合わせることで、MATCH関数がすべての条件を満たす行を特定し、INDEX関数がその行の値を取り出します。

出力結果：

129.99

XLOOKUPとINDEX-MATCHを使うメリット

VLOOKUPではなくXLOOKUPを選ぶ理由

• 双方向の検索が可能： XLOOKUPは左右どちらの方向でも検索できますが、VLOOKUPは左から右のみです。
• 列番号の指定が不要： 列番号を指定しないため、列構成の変更の影響を受けません。
• 既定で完全一致： XLOOKUPは既定で完全一致を検索するため、誤った結果を拾うリスクが減ります。
• ワイルドカード検索に対応： 文字列検索の際に * や ? などのワイルドカードを使用できます。
• 柔軟なエラー処理： 見つからなかった場合に返す値を自由に指定できます。

VLOOKUPより優れるINDEX-MATCHの利点

• 柔軟な表構造： 列の挿入や削除の影響を受けません。
• 左方向の検索が可能： VLOOKUPと異なり、検索列より左側の値も取得できます。
• パフォーマンス： 大規模データセットでは、MATCHの方がVLOOKUPの再計算よりも高速になる場合があり、効率的です。

まとめ

XLOOKUPやINDEX-MATCH-MATCHといった高度な検索テクニックは、Excelで高度なデータ処理を行ううえで不可欠です。これらの関数はデータの変更に自動的に追従し、従来のVLOOKUPを超える柔軟性・正確性・パフォーマンスを提供します。日々の業務で大量のデータを扱う方はもちろん、Excelスキルを一段階引き上げたいすべての方にとって、必ず習得しておきたいテクニックと言えるでしょう。

無料の高度なExcel演習問題と模範解答をぜひチェックしてみてください！