INDEX-MATCH-MATCHで双方向検索を行う

左側に地域が縦に並び、上部に月が横に並ぶ月別売上のグリッドを想像してください。選択した地域と月が交差する位置の数値を取得したいとします。

通常の検索では、1方向だけを検索して値を見つけます。一方、2方向検索では縦と横の両方を同時に検索し、正しい行と列を見つけて、その交差位置にある値を返します。

この処理に使われる古典的な方法が、2つのMATCHと組み合わせたINDEXで、一般にINDEX-MATCH-MATCHと書かれます。

INDEXは、位置を指定して範囲から値を返します。完全な形式はINDEX(array, row_num, column_num)です。

セルのブロック、行番号、列番号を渡すと、その位置にある値を返します。たとえばB2から始まるグリッドで行3、列2を指定すると、そのブロック内で3行下、2列右にある値が返されます。

重要なのは、INDEXが必要とするのは位置であり、ラベルではないという点です。その位置を提供するのがMATCHです。

MATCHは、1つの行または列の中で値の位置を検索します。形式はMATCH(lookup_value, lookup_array, match_type)です。

一致方法には0を指定して完全一致にします。結果は数値で、1から数えた値の位置を表します。

「East」が範囲A2:A5の2番目の項目なら、MATCHは2を返します。この2をINDEXの行番号として使えます。

2方向検索では、MATCHを2回実行します：

• 1つ目のMATCHで、地域が何行目にあるかを検索します。
• 2つ目のMATCHで、月が何列目にあるかを検索します。

次に、その2つの数値をINDEXに渡します。行用のMATCHはラベルの縦方向の範囲を検索し、列用のMATCHは見出しの横方向の範囲を検索します。

結果として、その行と列が交差する1つのセルが返されます。

次のレイアウトを考えてみましょう。地域ラベルはA2:A5（East、West、North、South）にあり、月の見出しはB1:D1（Jan、Feb、Mar）にあります。実際の売上数値はB2:D5に入力されています。

検索を制御する入力セルは2つあります。G1には検索する地域を、G2には検索する月を入力します。

目的は、G1とG2を読み取り、B2:D5から一致する売上額を返す1つの数式を作ることです。

まず地域を特定します。MATCHで、縦方向のラベル一覧A2:A5からG1に入力された値を検索します。

G1に「North」が含まれ、Northが3番目のラベルなら、このMATCHは3を返します。

この数値によって、INDEXがデータブロックのどの行を読み取るかが決まります。データではなく、ラベルだけを含むA2:A5を検索している点に注目してください。これにより、位置3がデータの3行目と対応します。

次に月を特定します。このMATCHでは、横方向の見出し行B1:D1からG2の値を検索します。

G2に「Feb」が含まれ、Febが2番目の見出しなら、MATCHは2を返します。

この数値がINDEXの列位置になります。行の場合と同様に、見出しだけを含むB1:D1を検索するため、その位置がB2:D5のデータ列と対応します。

ここで、2つのMATCHをINDEXの中に組み込みます。データブロックB2:D5が配列、行のMATCHが行番号、列のMATCHが列番号を指定します。

G1が「North」、G2が「Feb」の場合、行のMATCHは3、列のMATCHは2を返すため、INDEXはB2:D5の3行目、2列目の値を返します。

これで完全な2方向検索の数式になります。

B2:D5に、Northの行としてJan 50、Feb 80、Mar 65が入っているとします。

• MATCH("North", A2:A5, 0)は3を返します。
• MATCH("Feb", B1:D1, 0)は2を返します。
• INDEX(B2:D5, 3, 2)は3行目、2列目を読み取り、80を返します。

G1を「East」に変更したり、G2を「Mar」に変更したりすると、数式全体がすぐに再計算されます。これが、2つのMATCH検索を使ってINDEXを動かす強みです。

VLOOKUPは最初の列だけを検索し、そこから右へ固定数の列だけ離れた位置にある値を返します。月を切り替えるには、列番号を自分でハードコードするか計算する必要があります。

INDEX-MATCH-MATCHでは、行と列の両方をラベルによって動的に選択できます。列の順序を入れ替えたり、新しい月を追加したりしても、固定数ではなく見出しのテキストを照合するため、数式はそのまま機能します。

最もよくある間違いは、範囲が一致していないことです。行のMATCH範囲はINDEXのデータブロックと同じ高さでなければならず、列のMATCH範囲は同じ幅でなければなりません。

ここではA2:A5が4行で、B2:D5も4行なので、MATCHの結果が3なら、実際にデータの3行目を意味します。誤って見出しを含むA1:A5を検索すると、位置が1行ずれて、間違ったセルが返されます。

2方向検索のパターンを学びました。

• INDEXは、ブロック内の行位置と列位置にある値を返します。
• 1つ目のMATCHは、縦方向のラベルを検索して行を見つけます。
• 2つ目のMATCHは、横方向の見出しを検索して列を見つけます。

組み合わせた数式=INDEX(data, MATCH(row), MATCH(col))は2つの入力を読み取り、それらが交差する位置の値を返します。位置がずれないよう、MATCHの範囲はデータブロックと同じサイズにしてください。