VLOOKUP 関数は Excel で値を検索するのに適していますが、制限もあります。代わりに INDEX 関数と MATCH 関数を組み合わせて使用すると、スプレッドシート内の任意の場所または方向の値を検索できます。
INDEX 関数は、数式に入力した位置に基づいて値を返しますが、MATCH 関数はその逆を行い、入力した値に基づいて位置を返します。これらの機能を組み合わせると、必要な数字やテキストを見つけることができます。
VLOOKUP と INDEX および MATCH の比較
これらの関数と VLOOKUP の違いは、VLOOKUP は左から右に値を検索することです。したがって、この関数の名前が付けられました。 VLOOKUP は垂直方向の検索を実行します。
VLOOKUP の仕組みについては Microsoft が最もよく説明しています。
VLOOKUP の使用には特定の制限があります。VLOOKUP 関数は値を左から右にのみ検索できます。これは、検索する値を含む列が常に戻り値を含む列の左側に配置される必要があることを意味します。
Microsoftは続けて、VLOOKUPが必要なものを見つけるのに役立つようにシートが設定されていない場合は、代わりにINDEXとMATCHを使用できると述べています。それでは、Excel で INDEX と MATCH を使用する方法を見てみましょう。
INDEX 関数と MATCH 関数の基本
これらの機能を組み合わせて使用するには、その目的と構造を理解することが重要です。
配列形式の INDEX の構文は次のとおりです。
INDEX( array , row_number, column_number)
最初の 2 つの引数は必須で、3 番目の引数はオプションです。
INDEX は位置を検索し、その値を返します。セル範囲 D2 ～ D8 の 4 行目の値を見つけるには、次の数式を入力します。
=INDEX(D2:D8,4)
結果は 20,745 になります。これは、セル範囲の 4 番目の位置の値だからです。
INDEX の配列形式と参照形式、およびこの関数のその他の使用方法の詳細については、「 Excel での INDEX の使い方」を参照してください。
MATCH の構文は
MATCH(value, array, match_type)
で、最初の 2 つの引数は必須で、3 番目の引数はオプションです。
MATCH は値を検索し、その位置を返します。 A2 から A8 までの範囲内のセル G2 の値を見つけるには、次の数式を入力します。
=MATCH(G2,A2:A8)
セル G2 の値がセル範囲の 4 番目の位置にあるため、結果は 4 になります。
match_type
引数とこの関数のその他の使用方法の詳細については、
Excel での MATCH
のチュートリアルを参照してください。
Excel で INDEX と MATCH を使用する方法
各関数の動作とその構文がわかったので、次はこの動的な組み合わせを動作させます。以下では、上記と同じデータを INDEX と MATCH に個別に使用します。
MATCH 関数の数式を、検索する位置の代わりに INDEX 関数の数式内に配置します。
場所 ID に基づいて値 (売上) を見つけるには、次の式を使用します。
=INDEX(D2:D8,MATCH(G2,A2:A8))
結果は 20,745 です。 MATCH は、A2 ～ A8 の範囲内のセル G2 の値を検索し、それを INDEX に提供します。INDEX はセル D2 ～ D8 で結果を調べます。
別の例を見てみましょう。一定の金額に見合った売上高がある都市を知りたいのです。シートを使用して、次の式を入力します。
=INDEX(B2:B8,MATCH(G5,D2:D8))
結果はヒューストン。 MATCH は、D2 ～ D8 の範囲内のセル G5 の値を検索し、それを INDEX に提供します。INDEX はセル B2 ～ B8 で結果を調べます。
以下は、セル参照の代わりに実際の値を使用する例です。次の式を使用して、特定の都市の価値 (売上) を探します。
=INDEX(D2:D8,MATCH("ヒューストン",B2:B8))
MATCH 式では、ルックアップ値を含むセル参照を、B2 から B8 までの「ヒューストン」という実際のルックアップ値に置き換えました。これにより、D2 から D8 までの結果 20,745 が得られます。
セル参照ではなく実際の値を使用して検索する場合は、ここに示すように値を引用符で囲むようにしてください。
都市の代わりに場所 ID を使用して同じ結果を得るには、式を次のように変更するだけです。
=INDEX(D2:D8,MATCH("2B",A2:A8))
ここでは、セル範囲 A2 ～ A8 で「2B」を検索し、その結果を INDEX に提供するように MATCH 式を変更し、20,745 を返します。
セルに 数値を追加したり 、 現在の日付を入力したりする ための Excel の基本関数 は確かに役立ちます。しかし、さらにデータを追加し始めて、データ入力や分析のニーズを進めていくと、Excel の INDEX や MATCH などの検索関数が非常に便利になります。
