Index-matchを使用してexcelでvlookupを逆にする方法は?
INDEX と MATCH が逆引き参照式としてどのように機能するかを理解するには、1 つの単純なことを選択する必要があります。つまり、Match は列または行内の値の位置 (セル番号) をインデックスに伝え、インデックスはこの値を返します。この位置 (セル番号) を使用する値。
このように考えると、MATCH 機能は犯人を見つける潜入捜査官であり、INDEX 機能はその後その犯人を逮捕する警察官です。
ただし、これら 2 つの関数を組み合わせる方法を詳しく見てみましょう。以下はご存知のとおり、INDEX の構文です。
INDEX(配列, row_num, [column_num])
INDEX 関数では、row_num 引数によって、どの行から値を返す必要があるかがわかります。 4 を入力すると 4 行目の値が返されるとします。
逆引き参照式を作成するには、MATCH を INDEX の row_argument に置き換える必要があります。
MATCH を使用すると、検索列内の値が検索され、その値のセル番号が返されます。次に、INDEX はこの数値を使用して、値列からセルの位置を決定します。
最終的にはそのセルの値が返され、探している値が得られます。ここで、実際の例を見てみましょう。以下に都市とそこで働く従業員の名前のリストを示します。
ここでは、ムンバイで働いている従業員の名前を検索する必要があります。ここで、データを見ると、都市が含まれる列がルックアップ列であり、従業員名が含まれる列が値列です。そして、式は次のようになります。
=INDEX(B2:B11,MATCH(E3,A2:A11,0),0)
この式を理解するために、2 つの部分に分解してみましょう。
パート 1 : 最初のパートでは、match 関数を使用して値「Mumbai」を検索し、都市列の値「Mumbai」が含まれるセルの位置である「5」を返しました。
パート 2 : パート 2 では、INDEX を使用し、従業員名列を参照して値を検索しました。ここで、インデックス関数は、列の 5 番目のセルの値が必要であることを認識しています。したがって、結果として「Siya」が返されました。
INDEX とマッチングのその他の例
私たちの前には、INDEX MATCH 式を使用して解決した一般的な問題がいくつかあります。サンプル ファイルにアクセスします。各例を確認するには、必ずここからサンプル ファイルをダウンロードしてください。
1. INDEX – MATCHによる基本的な検索
通常の検索は、検索式を使用して実行する必要がある最も重要なタスクの 1 つであり、INDEX MATCH はそれに最適です。ここには、従業員 ID と名前を含むデータ テーブルがあります。各 ID は一意であるため、従業員の名前を ID で検索する必要があります。
EMP-132 という名前を検索するとします。この場合、式は次のようになります。
=INDEX(name_column,MATCH(emp-id,emp-id_column,0))
これがこの式の仕組みです
まず、MATCH は emp id 列の emp id と一致し、探している id のセル番号を返します。ここでは行番号は 6 です。
その後、 INDEX は同じセル番号を使用して名前列から従業員名を返します。
2. 左を見る
VLOOKUP では、値を検索するときに左に進むことはできません。前述したように、INDEX と MATCH では任意の方向に検索できます。以下のデータテーブルでは、金額列の後に請求書番号列があります。
したがって、特定の請求書の金額を検索したい場合、VLOOKUP では検索できません。 VLOOKUP では、テーブルを選択すると、そのテーブルの最初の列がルックアップ列になります。
ただし、このテーブルでは、テーブルの最後の列をルックアップ列として使用する必要があります。ここでの VLOOKUP については評価を下げます。レスキューのために INDEX と MATCH を呼び出してみましょう。式は次のようになります。
=INDEX(G2:G14,MATCH(L6,J2:J14,0),0)
…これがこの式の仕組みです
- まず、 index 関数の amount 列を参照しました。これは値を取得する必要がある列です。
- 次に、index 関数の row_number 引数で、match 関数を使用して請求書番号を指定し、請求書の列を参照し、完全一致には 0 を使用しました。
- 3 番目に、match 関数は範囲から請求書のセル番号を返します。
最後に、INDEX はこの数値を使用して、金額列からセルを配置することで金額を返します。
3.ファジーサーチ
VLOOKUPと同じように、INDEX/MATCHでも大まかな検索ができます。
あいまい検索は、探している値がリストされておらず、最も近い値を取得したい場合に便利です。下の表には、グレードに基づいたグレードのリストがあります。
また、79 のスコアを取得したい場合は、次の式を使用できます。
=INDEX(B2:B6,MATCH(D3,A2:A6,1))
…これがこの式の仕組みです
この式では、 match_typeの match 関数に 1 を使用して、近似検索を実行できるようにしています。検索値以下の最初の値を返します。
79 の場合、最初に低い値は 75 で、75 の場合、グレードは B になります。そのため、結果で B が得られます。
4. 水平検索
ご存知のとおり、HLOOKUP は水平方向の検索ですが、INDEX や MATCH も使用できます。以下のデータ テーブルには月ごとの売上を示す横のテーブルがあり、「5 月」の売上値を取得したいとしています。
そして、式は次のようになります。
=INDEX(amount,0,MATCH(lookup_month,months,0))
…これがこの式の仕組みです
上の式では、インデックスの row_num 引数で MATCH を使用する代わりに、column_num で MATCH を使用しました。そして、match は 5 月の列番号を返します。
そして、INDEX は位置番号に基づいて結果列の値を返します。
5.双方向検索
双方向検索では、テーブルから値を取得する必要があります。地域別、製品別の売上高を示した下の表をご覧ください。
特定の地域の製品の売上高を取得したい場合は、双方向検索が必要であり、そのためには INDEX MATCH MATCH を組み合わせて使用する必要があります。はい、ここでは MATCH を 2 回使用する必要があります。
INDEX と MATCH の通常の組み合わせでは、行番号に MATCH を使用しますが、双方向検索では列番号にも MATCH を使用する必要があります。式は次のようになります。
=INDEX(A1:F5,MATCH(H3,A1:F1,0),MATCH(H2,A1:A5,0))
…これがこの式の仕組みです
上の式では、テーブルから売上高を取得するために、index 関数を使用し、次に row_num 引数と column_num 引数に match 関数を使用しました。 column_num 引数である match 関数は、参照した範囲の 5 行目の Product-D 値として 5 を返します。
また、北ゾーンの値は参照した範囲の 2 列目にあるため、row_num 引数で見つかった match 関数は 2 を返します。
これらの値を使用して、インデックス関数は 2 列目、5 行目の値 1456 を返しました。
6. 大文字と小文字を区別する
リストまたは列に 2 つの同じ値があり、テキストの大文字と小文字が異なるときに問題が発生した場合は、大文字と小文字を区別した検索を実行して正しい値を見つけることができます。以下の生徒のリストで、下の名前があり、2 番目の列にマークを付けたところを見てみましょう。
そして、最初に、同じだがテキストケースが異なる名詞があります。たとえば、ジョン・パーカーやジョン・マシューなどです。 「John」ではなく「JOHN」のブランドを検索したいとします。INDEX と MATCH を使用して完全一致検索を作成できます。そして、式は次のようになります。
=INDEX(B2:B21,MATCH(TRUE,EXACT(E5,A2:A21),0))
…これがこの式の仕組みです
この数式では、match 関数で EXACT 関数を使用しています。 match 関数は大文字と小文字を区別する値を検索できないため、EXACT はこれに最適な関数です。
2 つの値を比較し、それらが完全に同じである場合 (大文字と小文字の区別を含む) に TRUE を返すことができますが、列全体を 1 つの値と EXACT で比較する必要があるため、この数式は表形式で入力する必要があります。入力するとこのような配列が返されます。
=INDEX(Sheet1!$B$2:$B$21,MATCH(TRUE,{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE},0))
そこから、配列から TRUE の位置を取得するために match 関数を使用する必要があります。
この時点で、検索する必要がある値のセル番号 (行番号) がわかります。また、この列の値には INDEX を使用できます。
重要な点: EXACT で複数の TRUE を取得した場合、一致では最初の TRUE の数値が返されます。
7. INDEX MATCH でワイルドカードを使用する
ワイルドカードは非常に便利です。ワイルドカードを使用して部分検索を実行できます。そして、最も優れている点は、他のすべての数式検索と同様に、インデックスと一致でワイルドカードを使用できることです。
従業員の姓名と年齢が記載されている下の名前リストを見てください。
このリストから、特定の従業員 (Sondra) の年齢を取得する必要があります。しかし実際には、名前しか知らないのです。
また、アスタリスクを使用すると、名を使用してソンドラの年齢を検索できます。この場合、式は次のようになります。
=INDEX(B2:B11,MATCH("Sondra*",A2:A11,0),0)
…これがこの式の仕組みです
アスタリスクは、n 個の文字を置き換えることができるワイルドカード文字です。したがって、名前の後に使用すると、姓が置き換えられます。
8. 最低値
以下のようなスコアを含む生徒のリストがあるとします。次に、このリストからスコアが最も低い生徒の名前を検索します。
このために、MIN 関数をインデックスと一致とともに使用することができ、式は次のようになります。
=INDEX(A2:A21,MATCH(MIN(B2:B21),B2:B21,0))
A 列に生徒の名前があり、B 列に生徒の得点が表示されます。
したがって、この数式をセルに挿入して Enter キーを押すと、スコアが最も低い生徒の名前、つまり Librada Bastian が返されます。
説明
この式には 3 つの異なる部分があります。
最初の部分では、MIN 関数は最低スコアを返します。
その後、2 番目の部分で、match 関数はその最低スコアのセルを返します。
最終的に、index 関数は、match によって返された同じセル位置を使用して、生徒名列の値を返します。
ヒント:同様に、最高点を獲得した生徒の名前を取得することもできます。
9. n 番目の最高スコア
ここで次のように考えてください。試験の得点を含む学生のリストがあり、このリストから 2 番目に高い得点を獲得した学生の名前を取得したいとします。
問題は、2 番目に高いスコアが何なのかはわからないということです。
通常、数式検索で値を検索すると、探している値が確実に得られます。ただし、ここでは 2 番目に高いスコアが何であるかはわかりません。
したがって、このために、大きな関数とインデックスを組み合わせて一致させることができます。大きい関数は、範囲内で 2 番目に高い値を決定するのに役立ちます。
そして、式は次のようになります。
=INDEX(student_names,MATCH(LARGE(score,2),score,0))
…これがこの式の仕組みです
この数式では、lookup_value 引数の match 関数内の大きな関数を使用しました。そして、大きな関数では、スコア範囲と 2 番目に高い値を取得する 2 について言及しました。
Large 関数が 2 番目に大きい値を返した後、match 関数はその値を使用して、対応するセル番号を返します。
最後に、index 関数はこのセル番号を使用して生徒の名前を返します。
10. 複数の基準
通常、index と match の組み合わせは、単一の値を検索することを目的としています。これが、match 関数で範囲を 1 つだけ使用する理由です。
ただし、現実世界でデータに直面する場合、値を見つけるために複数の基準を使用する必要がある場合があります。
以下の例を考えてみましょう。ここには、製品名、カテゴリ、サイズなどの詳細が記載された製品のリストがあります。
そして、このデータから、すべての基準を使用して特定の製品の価格を取得したいとします。
したがって、式は次のようになります。
=INDEX(D2:D11,MATCH(1,(A14=A2:A11)*(B14=B2:B11)*(C14=C2:C11),0))
注:これは配列数式なので、Ctrl + SHIFT + Enter を使用して入力する必要があります。
…これがこの式の仕組みです
この数式では、3 つの異なる値に一致する 3 つの異なる配列があり、これらの配列は値が一致する場合に TRUE と FALSE を返します。
その後、それらを互いに乗算すると、配列またはそのようなものが得られます。
=INDEX(D2:D11,MATCH(1,{0;0;0;0;0;0;1;0;0;0},0))そして、match 関数は配列の 1 の位置を返します。
最後に、INDEX は、一致によって返された数値を使用して、価格列の価格を返します。
ヒント:配列数式を使用したくない場合は、 SUMPRODUCT 条件を使用できます。
11. 範囲の最初の数値
テキストと数値の両方を含むリストがあり、このリストから最初の数値を取得するとします。
これを行うには、ISNUMBER 関数とインデックス/一致を組み合わせます。 ISNUMBER は、どの値が数値でどの値がテキストであるかを識別するのに役立ちます。
式は次のようになります。
=INDEX(list,MATCH(TRUE,ISNUMBER(list),0))
この数式は表形式で入力する必要があります (Ctrl + Shift + Enter を使用)。
…これがこの式の仕組みです
この式では、ISNUMBER はリストの長さに等しい配列を返します。この配列では、数値である値に対して TRUE が返され、残りの値に対して FALSE が返されます。
その後、match関数で検索値としてTRUEを使用しました。したがって、配列内の最初の TRUE の位置番号を返します。
最終的に、この位置番号インデックスを使用すると、最初の数値が返されます。
12. 最初の空でない値を取得する
このように考えてみましょう。最初のセルの一部が空である値のリストがあり、空ではない最初の値を取得したいとします。
そして、この式を使用して、この最初の空でない値を取得できます。
=INDEX(A2:A11,MATCH(FALSE,ISBLANK(A2:A11),0))
…これがこの式の仕組みです
この公式を賢く理解するには、この公式を 3 つの異なる部分に分割する必要があります。
まず、match 関数で ISBLANK 関数を使用して、空のセルの場合は TRUE、空でないセルの場合は FALSE を持つ配列を取得しました。
次に、MATCH は、ISBLANK によって返される配列内の最初の TRUE の位置番号を返します。
したがって、この時点で、空白ではない最初の値のセル番号がわかります。
3 番目に、インデックス関数は単にリスト内の最初の空でない値を返します。
13. 最も頻繁に使用されるテキスト
ここで、テキスト値のリストが与えられ、最も頻繁に使用されるテキストをカウントする必要があるとします。
以下のリストに名前があります。
ただし、複数回登場する名前もあります。
したがって、リスト内で最も多く出現する名前を取得する必要があります。 MODE、INDEX、MATCH を組み合わせた以下の式を使用できます。
=INDEX(A2:A13,MODE(MATCH(A2:A13,A2:A13,0)))
この数式を挿入すると、最も一般的な名前である「Tamesha」が返されます。
…これがこの式の仕組みです
まず、MATCH は名前の範囲全体をそれ自体と比較します。そして、そうすることで、各テキストが最初の位置を表す配列を返します。
リストで最も一般的な名前である「Tamesha」という名前の例を見てみましょう。ここでリストを見ると、これは最初に 8 番目のセルで発生し、次に 12 番目のセルで発生しました。
しかし、テーブルを見ると、「Tamesha」があるすべてのポジションで、最初のポジションである 8 が返されました。
その後、モード関数は MATCH によって返された配列から、最初に「 Tamesha 」が出現したセル番号である最頻値を返します。
そして最後に、INDEX はこのセル番号を使用してテキストを返します。
14. ハイパーリンクを作成する
ここで、値の検索に加えて、その値のハイパーリンクも作成したいとします。こうすることで、検索列が配置されているセルにすばやく移動できます。
たとえば、以下の表では、人の年齢を取得する必要があります。また、この値のハイパーリンクを作成すると、この値が存在するセルに簡単に移動できます。
このためには、HYPERLINK + Cell と INDEX および MATCH を使用する必要があり、式は次のようになります。
この式はこのように機能します
より深く理解するために、この式をいくつかの部分に分けてみましょう。
- まず、セル関数でindexとmatchを使用しました。また、セル関数で両方の関数を使用すると、対応する値ではなく、対応する値のセル参照が取得されます。
- 次に、「#」とセル参照を連結しています。
- 3 番目に、index と match を再度使用して、リンク テキストとして使用する一致する値を取得しました。これにより、対応する値と、その値が配置されているセルへのリンクが得られます。