【Excel】XLOOKUPが使えない旧Excel環境での代替手段!INDEX/MATCHで検索を実現する方法

Excelで特定の条件に合うデータを検索する際、XLOOKUP関数は非常に強力な機能です。しかし、古いバージョンのExcelを使用している場合、XLOOKUP関数は利用できません。このような環境で、XLOOKUPと同様の検索機能を実現するにはどうすれば良いでしょうか。本記事では、XLOOKUPが使えない環境で活用できるINDEX関数とMATCH関数の組み合わせによる代替方法を解説します。この方法を習得すれば、旧Excel環境でも効率的にデータを検索できるようになります。

INDEX/MATCH関数がXLOOKUPの代替となる仕組み

XLOOKUP関数は、指定した検索値が、検索範囲のどこにあるかを見つけ出し、その位置に対応する取得範囲から値を取り出す機能を持っています。この「検索値の位置を見つける」役割と「その位置の値を取り出す」役割を、それぞれMATCH関数とINDEX関数が担当します。MATCH関数は、検索対象のリスト内で、探したい値が何番目にあるかを数値で返します。INDEX関数は、指定された表(配列)の中から、行番号と列番号を指定して、その交点にあるセルの値を返します。この二つを組み合わせることで、XLOOKUP関数がなくても、目的のデータを検索できるようになるのです。

INDEX/MATCH関数で検索を実現する手順

INDEX関数とMATCH関数を組み合わせて、XLOOKUP関数のようにデータを検索する具体的な手順を説明します。ここでは、商品IDを元に商品名を取得する例を用います。

1. 検索したい商品IDを入力する
   検索結果を表示したいセルに、検索したい商品IDを入力します。例えば、セルE2に商品IDを入力するとします。
2. MATCH関数で商品IDの位置を特定する
   次に、MATCH関数を使って、入力した商品IDが商品IDリストの何番目にあるかを探します。例えば、商品IDがA列(A2:A10)、商品名がB列(B2:B10)にある場合、商品IDが入力されているセルE2の値が、A2:A10の範囲の何番目にあるかを検索するには、以下の数式を入力します。

   =MATCH(E2, A2:A10, 0)

   この数式は、E2に入力された値(検索したい商品ID)を、A2からA10の範囲で検索し、完全一致するものが何番目にあるかを返します。最後の「0」は完全一致を指定するための引数です。この結果、例えば商品ID「103」が3番目にある場合、「3」が返されます。

3. INDEX関数で商品名を取得する
   MATCH関数で特定した位置情報(行番号)を使い、INDEX関数で対応する商品名を取得します。商品名がB列(B2:B10)にある場合、MATCH関数の結果(行番号)を使って、B列から該当する商品名を取得するには、以下の数式を入力します。

   =INDEX(B2:B10, MATCH(E2, A2:A10, 0))

   この数式は、B2からB10の範囲内で、MATCH関数が返した行番号(この例では3)に対応する値を取得します。つまり、3番目の商品名が返されます。

4. 検索結果を確認する
   上記数式を入力したセルに、商品IDに対応する商品名が表示されれば、検索は成功です。検索したい商品IDをE2セルで変更すれば、自動的に対応する商品名が更新されます。

INDEX/MATCH関数を使った検索の応用

INDEX関数とMATCH関数の組み合わせは、単一の条件での検索だけでなく、複数の条件を組み合わせた検索にも応用できます。また、検索方向を限定しないため、VLOOKUP関数のように検索列を一番左に配置する必要もありません。ここでは、応用例として二つの条件で検索する方法と、検索範囲の指定方法について解説します。

複数の条件で検索する方法

複数の条件で検索したい場合、MATCH関数の検索対象を工夫します。具体的には、検索したい複数の条件を掛け合わせた一時的な配列を作成し、その配列内で検索します。例えば、商品ID(A列)と色(C列)の二つの条件で商品名(B列)を検索する場合を考えます。

検索したい商品IDをE2セル、検索したい色をF2セルに入力するとします。この場合、MATCH関数で、A列とC列の値を掛け合わせた配列の中で、E2とF2の値を検索します。数式は以下のようになります。

=INDEX(B2:B10, MATCH(E2&F2, A2:A10&C2:C10, 0))

この数式では、「E2&F2」で検索したい商品IDと色を連結し、「A2:A10&C2:C10」で各行の商品IDと色を連結した配列を作成しています。MATCH関数は、この連結された配列の中から、連結された検索値と一致する行番号を返します。INDEX関数は、その行番号に対応する商品名を取得します。この数式は配列数式として扱われるため、Excelのバージョンによっては数式入力後にCtrl+Shift+Enterで確定する必要があります(Microsoft 365では不要な場合があります)。

検索範囲の指定方法

INDEX関数とMATCH関数を組み合わせる際の検索範囲の指定には、いくつかの注意点があります。MATCH関数は、検索範囲全体の中で検索値が何番目にあるかを返します。そのため、INDEX関数で指定する取得範囲は、MATCH関数が返す行番号と正しく対応している必要があります。

例えば、商品IDがA列(A2:A10)にあり、商品名がC列(C2:C10)にある場合、MATCH関数でA2:A10の範囲を検索し、INDEX関数でC2:C10の範囲から値を取得します。このとき、MATCH関数で指定する範囲とINDEX関数で指定する範囲の開始行が一致していることが重要です。もし、MATCH関数の検索範囲がA3:A10で、INDEX関数の取得範囲がC2:C10であった場合、MATCH関数が「1」を返しても、INDEX関数はC2の値ではなくC3の値を取得しようとするため、意図しない結果になる可能性があります。

INDEX/MATCH関数利用時の注意点とよくある失敗

INDEX関数とMATCH関数を組み合わせた検索は強力ですが、いくつかの注意点と、それに伴うよくある失敗パターンが存在します。これらを理解しておくことで、より正確で効率的なデータ検索が可能になります。

検索値が見つからない場合のエラー(#N/A)

MATCH関数は、検索値が検索範囲内に見つからない場合、#N/Aエラーを返します。これにより、INDEX関数も#N/Aエラーを返すため、検索結果が表示されなくなります。このエラーを回避するには、IFERROR関数を組み合わせるのが一般的です。

例えば、上記で説明した基本的な検索数式にIFERROR関数を適用する場合、以下のようになります。

=IFERROR(INDEX(B2:B10, MATCH(E2, A2:A10, 0)), "見つかりません")

これにより、検索値が見つからなかった場合に「見つかりません」と表示され、エラーメッセージがそのまま表示されるのを防ぐことができます。表示したいメッセージは自由に設定可能です。

検索範囲の絶対参照・相対参照の誤り

INDEX関数やMATCH関数で指定する検索範囲(配列)をコピーして使用する場合、参照の指定方法が重要になります。通常、数式をコピーすると、参照範囲も相対的に変化します。

しかし、検索範囲は固定したい場合がほとんどです。この場合、参照範囲を絶対参照(例: `$A$2:$A$10`)にする必要があります。MATCH関数とINDEX関数の両方で、検索範囲と取得範囲に絶対参照を設定しないと、数式をコピーした際に意図しない範囲を参照してしまい、誤った結果が表示される原因となります。

検索モードの指定ミス(完全一致以外)

MATCH関数には、検索モードを指定する3番目の引数があります。この引数で「0」を指定すると完全一致検索になりますが、これを省略したり、「1」や「-1」を指定したりすると、近似一致検索や逆順での検索となり、意図しない結果を返すことがあります。

特に、データが昇順に並んでいない場合に「1」を指定したり、降順に並んでいない場合に「-1」を指定したりすると、正しい結果が得られません。XLOOKUP関数のデフォルトは完全一致ですが、INDEX/MATCHの組み合わせでは明示的に「0」を指定する必要があるため、この点を忘れると失敗の原因となります。

INDEX/MATCHとXLOOKUP、VLOOKUPとの比較

Excelの検索機能には、XLOOKUP関数、INDEX/MATCH関数、VLOOKUP関数などがあります。それぞれの特徴と使い分けについて比較します。

項目	XLOOKUP関数	INDEX/MATCH関数	VLOOKUP関数
検索方向	双方向(左右、上下)	双方向(左右、上下)	左から右のみ
複数条件検索	可能(AND条件)	可能(配列数式)	不可能(別途工夫が必要)
検索値が見つからない場合	指定可能(IFERROR相当)	IFERROR関数で対応	指定可能(IFERROR相当)
検索列の配置	問わない	問わない	検索列は一番左にある必要あり
新旧バージョン対応	Microsoft 365およびExcel 2021以降	全バージョン対応	全バージョン対応
数式の複雑さ	シンプル	やや複雑	比較的シンプル

XLOOKUP関数は、最も高機能で使いやすいですが、利用できるバージョンが限られます。INDEX/MATCH関数は、古いバージョンでも利用でき、検索方向や複数条件検索の柔軟性が高いですが、数式がやや複雑になります。VLOOKUP関数は、広く使われていますが、検索列の配置に制約があり、柔軟性に欠けます。利用できるExcelのバージョンや、実現したい検索の複雑さに応じて、最適な関数を選択することが重要です。

まとめ

本記事では、ExcelのXLOOKUP関数が使えない旧環境において、INDEX関数とMATCH関数を組み合わせることで、同様の検索機能を実現する方法を解説しました。MATCH関数で検索値の位置を特定し、INDEX関数でその位置の値を取得する仕組みを理解すれば、旧バージョンのExcelでも効率的なデータ検索が可能になります。検索値が見つからない場合のエラー処理や、範囲指定の注意点も把握しておくことで、より確実なデータ処理が行えるでしょう。今後は、利用可能なExcelのバージョンに合わせて、XLOOKUP関数、INDEX/MATCH関数、VLOOKUP関数を使い分けることで、さらに高度なデータ分析に挑戦してみてください。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】