INDEX + MATCH:Excel 2016以降対応の王道検索テクニック
💡 やりたいこと
「検索キーより左側にある列」のデータを取得したいが、XLOOKUPが存在しない旧バージョンのExcelを使っている。
列を挿入・移動しても数式がずれにくい、安全な検索の仕組みを作りたい。
Excel 2016以降対応の定番の組み合わせ:INDEX関数 + MATCH関数
VLOOKUP最大の弱点である「検索キーより右の列しか取れない」「列番号をハードコーディングするため、挿入削除で壊れる」という問題をスマートに解決してきた、Excelの歴史において最も有名な組み合わせです。
=INDEX(取得したい列, MATCH(検索値, 検索する列, 0))
=INDEX(A:A, MATCH(F2, C:C, 0))
意味:C列の中からF2の値を探し、それが上から何番目か(MATCH)を特定する。
そして、A列の「その行番号」にある値(INDEX)を取り出して表示する。
🔥 なぜVLOOKUPより優秀なのか?
・取得したい列(A列)と検索する列(C列)を独立して指定するため、列を挿入・移動してもずれにくくなります(VLOOKUPのように列番号を数え直す必要がありません)。ただし、参照している列そのものを削除すると
・検索キーが一番右にあっても左端のデータを難なく取得できます。
・取得したい列(A列)と検索する列(C列)を独立して指定するため、列を挿入・移動してもずれにくくなります(VLOOKUPのように列番号を数え直す必要がありません)。ただし、参照している列そのものを削除すると
#REF! になります。・検索キーが一番右にあっても左端のデータを難なく取得できます。
⚠️ MATCHの第3引数「0」を絶対に忘れないこと
, 0 を書き忘れると近似一致検索になってしまい、間違った行番号を返して誤った結果につながります。
さらに高度な実務テクニック
行オフセット(相対位置の取得)
「ヒットしたセルの1個下」を取得したい場合、VLOOKUPでは不可能ですが、この手法なら行番号に +1 するだけで簡単に実現できます。
=INDEX(A:A, MATCH(F2, C:C, 0) + 1)
行列のクロス検索(二次元検索)
「縦軸(社員名)」と「横軸(月)」の両方を同時に検索して交差点の値を取る場合も、この組み合わせを使います。
=INDEX(A1:M100, MATCH("田中", A1:A100, 0), MATCH("4月", A1:M1, 0))
✅ 最新環境(XLOOKUP)でのクロス検索
XLOOKUPでもネスト構造で同じことができます。
最新環境では基本的にXLOOKUPを推奨しますが、「行番号・列番号の数値自体」を取り扱いたいケースでは、現在でもINDEX+MATCHは現役の必須テクニックです。
XLOOKUPでもネスト構造で同じことができます。
=XLOOKUP("田中", A2:A100, XLOOKUP("4月", B1:M1, B2:M100))最新環境では基本的にXLOOKUPを推奨しますが、「行番号・列番号の数値自体」を取り扱いたいケースでは、現在でもINDEX+MATCHは現役の必須テクニックです。