【Googleスプレッドシート】INDEX&MATCHでVLOOKUP代替!列の位置に依存しない検索方法

VLOOKUP関数は便利ですが、検索する列の位置を数値で指定するため、列を挿入したり削除したりすると参照がずれてしまうという悩みがあります。INDEX関数とMATCH関数を組み合わせることで、列の位置に依存しない柔軟な検索が可能です。この記事では、INDEX&MATCHの基本的な使い方から、VLOOKUPとの違い、よくあるトラブルの対処法までを詳しく解説します。これを読めば、列の増減に強い検索を自在に組み立てられるようになります。

INDEXとMATCHの組み合わせがVLOOKUPより優れている理由

VLOOKUPは、検索値が範囲の左端列にあることを前提とし、取得したい値の列番号を数値で指定します。そのため、列を挿入すると列番号がズレてしまい、数式の修正が頻繁に発生します。一方、INDEX&MATCHは、MATCHで検索値の行位置を動的に特定し、INDEXでその行の任意の列を指定します。列の指定は列番号ではなく、範囲そのものを変えればよいため、列の増減に柔軟に対応できます。また、検索範囲が左端列でなくても動作するため、より自由度の高い検索が可能です。この仕組みを理解すれば、VLOOKUPの欠点を解消できるでしょう。

基本のINDEX&MATCHを使う手順

ここでは、商品コードから価格を検索する例を使って、基本的な手順を説明します。データはA列に商品コード、B列に商品名、C列に価格が入力されているとします。商品コード「A001」の価格を取得する場合、数式は「=INDEX(C:C, MATCH(“A001”, A:A, 0))」となります。

1. MATCHで目的の行番号を取得する
   「=MATCH(“A001”, A:A, 0)」と入力します。第1引数に検索値、第2引数に検索範囲(A列全体)、第3引数に0(完全一致)を指定します。これでA001が存在する行番号(例:2行目)が返ります。
2. INDEXでその行の値を取り出す
   先ほどのMATCHをINDEXの行番号に埋め込みます。「=INDEX(C:C, MATCH(“A001”, A:A, 0))」とします。INDEXの第1引数に取得したい列(C列)、第2引数に行番号を指定します。これで価格が返ります。
3. セル参照を使って汎用性を高める
   検索値を直接入力するのではなく、別セル(例:E1)に商品コードを入力し、「=INDEX(C:C, MATCH(E1, A:A, 0))」とします。検索値を変更するだけで自動的に結果が変わります。

応用編:列を動的に指定してさらに柔軟に

INDEX&MATCHの真価は、取得する列も動的に指定できる点にあります。たとえば、商品名と価格のどちらを取得するかを選択したい場合、MATCHをもう一つ使って列番号を特定します。

1. 取得する列名を入力するセルを用意する
   G1に「価格」と入力します。見出し行(1行目)に各列の名前があると仮定します。
2. MATCHで列番号を取得する
   「=MATCH(G1, 1:1, 0)」とします。これで「価格」という列名が何列目にあるかがわかります。
3. INDEXで動的に値を取得する
   「=INDEX(A:C, MATCH(E1, A:A, 0), MATCH(G1, 1:1, 0))」とします。INDEXの第3引数に列番号を指定します。これで、検索値と取得列を自由に変更できる柔軟な検索が完成します。

よくあるトラブルとその対処法

#N/Aエラーが表示される

MATCHで検索値が見つからない場合に発生します。検索値の前後に余計なスペースがないか確認してください。また、データ型が一致しているかも重要です。数値と文字列が混在していないか、数式で「–」を使って数値に変換する方法もあります。

意図しない行番号が返ってくる

MATCHの第3引数を0(完全一致)にしないと、近似一致で誤った位置を返すことがあります。必ず第3引数を0に設定してください。また、検索範囲に重複データがあると最初にヒットした行番号が返ります。重複がある場合は、一意なキーを使う工夫が必要です。

範囲の参照がズレる

データ範囲を絶対参照で固定しないと、数式をコピーしたときに範囲がズレます。INDEXの範囲とMATCHの範囲は、コピー先で変わらないように「$A$1:$A$100」のように絶対参照(F4キー)を使って固定しましょう。

VLOOKUPとINDEX&MATCHの比較

まとめ

INDEX&MATCHを使えば、列の位置に依存しない検索が実現でき、メンテナンス性が大幅に向上します。基本形「=INDEX(取得列, MATCH(検索値, 検索列, 0))」を覚えれば、VLOOKUPの不満を解消できます。さらに応用として、取得列を動的にするテクニックも試してみてください。次のステップとして、複数条件の検索(MATCHを&でつなぐ)や、XLOOKUPとの比較も検討すると、より幅広いシーンで活用できるでしょう。スプレッドシートの関数を組み合わせる力を身につけて、データ管理を効率化してください。