INDEXとMATCHを組み合わせる

これで、検索を構成する2つの要素がわかりました。MATCHは値がどこにあるかを見つけ、INDEXはその位置にある値を返します。

この2つを組み合わせると、完全な検索ができます。MATCHで行を特定し、INDEXでその行の任意の列からデータを取得します。

仕組みがわかればパターンは簡単です。通常は行番号を入れるINDEXの場所に、MATCHを入れ子にします。

これから何度も使う形式は次のとおりです。

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

内側から順に読みます。まずMATCHが実行され、位置番号を返します。その番号がINDEXのrow_numになり、返す範囲から値が取得されます。

通常、返す範囲と検索範囲の行数は同じなので、一方の範囲の位置がもう一方の範囲にも対応します。

A列に商品名、C列に価格が入った表を想像してください。「Cherry」の価格を取得したいとします。

まずMATCHでCherryを検索します。=MATCH("Cherry", A2:A20, 0)の結果は、たとえば3になります。

次にINDEXでその3を使います。=INDEX(C2:C20, 3)は、C列の3番目の行にある価格を返します。

2つを入れ子にすると、1つの数式で結果を取得できます。=INDEX(C2:C20, MATCH("Cherry", A2:A20, 0))です。

学習中は「Cherry」をハードコードしても問題ありませんが、実際の数式では代わりにセルを参照します。検索語をE1に入力し、そのセルを参照します。

=INDEX(C2:C20, MATCH(E1, A2:A20, 0))

これで、E1に入力した商品名の価格がすぐに返されます。Bananaと入力すればBananaの価格が返され、Dateと入力すれば結果が更新されます。

1つの数式が、入力セルだけで操作できる再利用可能な検索ツールになります。

INDEXには列番号も指定できます。その番号を2つ目のMATCHで取得します。これにより、行と列が交差する位置の値を特定できます。

=INDEX(B2:E10, MATCH(G1, A2:A10, 0), MATCH(G2, B1:E1, 0))

最初のMATCHはA列のラベルから行を検索し、2つ目のMATCHは1行目の見出しから列を検索します。INDEXは、それらが交差するセルを返します。この高度なパターンについては、後で詳しく説明します。

戻り範囲によって、何が返されるかが決まります。同じキーで検索する場合、INDEXの範囲を変更するだけで、必要な列の値を取得できます。

顧客のメールアドレスを検索するには、=INDEX(D2:D50, MATCH(E1, A2:A50, 0))とします。

同じ顧客の都市名を検索するには、=INDEX(F2:F50, MATCH(E1, A2:A50, 0))とします。

MATCHの部分は変わりません。別の値を選ぶためにINDEXの範囲だけを変更します。

INDEX には列番号も指定できます。列番号は2つ目の MATCH で求めます。これにより、行と列が交差する位置の値を特定できます。

=INDEX(B2:E10, MATCH(G1, A2:A10, 0), MATCH(G2, B1:E1, 0))

最初の MATCH は A 列のラベルから行を特定し、2つ目の MATCH は 1 行目の見出しから列を特定します。INDEX は、それらが交差するセルを返します。この高度なパターンについては、後ほど詳しく学びます。

位置を正しく対応させるには、検索範囲と戻り範囲の開始行を同じにし、高さも揃える必要があります。

MATCHがA2:A20（19行）を検索する一方で、INDEXがC2:C19（18行）から値を返すと、位置がずれて誤った結果になります。

確実な方法は、A2:A20とC2:C20のように、両方でまったく同じ行範囲を使うことです。A:AやC:Cのような列全体の参照を使えば、自動的に位置が揃います。

MATCHで検索値が見つからない場合、#N/Aが返され、INDEX-MATCH全体にもそのエラーが表示されます。IFNAで囲むと、すっきりした代替結果を指定できます。

=IFNA(INDEX(C2:C20, MATCH(E1, A2:A20, 0)), "Not found")

これで、見つからない商品には不安を招くエラーではなく、「Not found」というテキストが表示されます。IFERRORも使えますが、IFNAは見つからない場合だけを対象にし、それ以外のエラーは表示したままにできます。

ここまでの内容をすべて組み合わせてみましょう。社員テーブルがあり、A列にID、B列に名前、C列に部署、D列に給与が入っているとします。ユーザーはG1にIDを入力します。

その社員の部署を返すには、=INDEX(C2:C200, MATCH(G1, A2:A200, 0))とします。

代わりに給与を返すには、INDEXの範囲をD2:D200に変更します。検索の仕組みは変わらず、読み取る列だけが変わります。これは、動的な検索で日常的に使える定番の方法です。

数式が難しそうに見えるときは、スプレッドシートと同じように、最も内側の関数から外側へ向かって評価します。

=INDEX(C2:C20, MATCH(E1, A2:A20, 0))の場合、まずMATCH(E1, A2:A20, 0)を読み、5のような数値を返すと考えます。次に、それを頭の中で置き換えると、=INDEX(C2:C20, 5)になります。

すると数式は、単に「5番目の価格を返す」という意味になります。この習慣があれば、入れ子になった検索も簡単にデバッグできます。

2つの関数がどのように組み合わさるかを確認しましょう。

2つの関数を組み合わせて、柔軟な検索を作成しました。

• パターン：=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
• MATCHが行の位置を検索し、INDEXがその位置の値を返します
• 検索列と戻り列は独立しているため、右方向だけでなく左方向にも簡単に検索できます
• 両方の範囲の高さを揃え、IFNAで囲んでエラーをきれいに処理します

次は、この方法がVLOOKUPより優れていることが多い理由を詳しく見ていきます。