今回は、ExcelのINDEX関数とMATCH関数を組み合わせて、行と列の条件から値を取り出す二方向参照の方法を紹介します。
二方向参照の考え方を押さえる
Excelで表から値を取り出すとき、行方向だけを検索するならVLOOKUPやXLOOKUPで対応できる場面が多くあります。ただ、行見出しと列見出しの両方を指定して、交差する位置の値を取り出したい場合は、二方向参照の考え方が役立ちます。
たとえば、商品名と月、社員名と評価項目、部署名と費目など、縦と横の見出しが交差する表があります。このような表では、行の位置と列の位置をそれぞれ探し、その交点にある値を返します。INDEX関数は指定した位置の値を返し、MATCH関数は見出しが何番目にあるかを調べます。この二つを組み合わせることで、表の交点を柔軟に参照できます。
二方向参照が役立つ場面は次の通りです。
- 月別の実績表から特定項目の値を取り出す
- 担当者別、項目別の一覧から交点を参照する
- 料金表や対応表から条件に合う値を取得する
- 表の列順が変わる可能性がある資料を扱う
二方向参照は、行と列を別々に探して交点を返す仕組みです。表の形を理解してから数式を作ると、後から修正しやすくなります。
INDEX関数の役割を理解する
INDEX関数は、指定した範囲の中から、何行目、何列目の値を返す関数です。単体で使う場合は、範囲、行番号、列番号を指定します。たとえば、一覧表の三行目、二列目の値を返す、といった考え方です。
二方向参照では、INDEX関数に「値を取り出す範囲」を渡し、行番号と列番号をMATCH関数で求めます。つまり、INDEX関数は最終的に値を返す役割を持ちます。ここで指定する範囲には、行見出しや列見出しを含めず、実際に取り出したい値の範囲だけを指定すると分かりやすくなります。
INDEX関数を使うときの確認点は次の通りです。
- 返したい値が入っている範囲だけを指定しているか
- 行番号と列番号がその範囲内の位置になっているか
- 見出し行や見出し列を含めて位置がずれていないか
- 表の左上を基準にして何行目、何列目かを考えているか
範囲の取り方がずれると、正しい見出しを検索していても別の値が返ることがあります。数式を作る前に、値の範囲、行見出しの範囲、列見出しの範囲を分けて確認しておくと、ミスを見つけやすくなります。
MATCH関数で見出しの位置を探す
MATCH関数は、指定した値が範囲の中で何番目にあるかを返します。二方向参照では、行見出しの中から対象の行番号を探し、列見出しの中から対象の列番号を探すために使います。
完全一致で探す場合は、検索方法に0を指定します。業務表では見出し名を正確に合わせて参照することが多いため、完全一致を使う場面が多くなります。近似一致を使う場合もありますが、料金表や区分表など、並び順と条件が明確な場合に限って使う方が安全です。
MATCH関数を使うときは、次の点を確認します。
- 検索値と見出しの表記が一致しているか
- 余分な空白が入っていないか
- 行見出しの範囲と列見出しの範囲を取り違えていないか
- 完全一致で探す場合は検索方法に0を指定しているか
見出しに全角と半角の違い、末尾の空白、似た表記が混ざっていると、検索できないことがあります。表記ゆれが起きやすい表では、入力規則や見出しの統一を先に行うと数式が安定します。
INDEXとMATCHを組み合わせる
二方向参照の数式では、INDEX関数の行番号と列番号にMATCH関数を入れます。行見出しから行番号を探し、列見出しから列番号を探し、その交点の値をINDEX関数で返す流れです。
作る順番としては、いきなり長い数式を完成させるより、行用のMATCH、列用のMATCHを別々に確認すると分かりやすくなります。それぞれが正しい番号を返していることを確認してからINDEX関数に入れると、エラーの原因を分けて探せます。
組み立ての流れは次の通りです。
- 値を取り出す本体範囲を決める
- 行見出しの範囲を決める
- 列見出しの範囲を決める
- 行条件をMATCH関数で探す
- 列条件をMATCH関数で探す
- INDEX関数に二つの位置を渡す
二方向参照では、三つの範囲の行数や列数の対応をそろえることが重要です。本体範囲、行見出し、列見出しの位置関係がずれると、正しい交点を参照できません。
条件入力用のセルを用意する
INDEXとMATCHの二方向参照は、条件をセルで指定すると使いやすくなります。たとえば、B2セルに商品名、C2セルに月を入力し、その組み合わせに合う値を表示するようにします。条件セルを変えるだけで結果が切り替わるため、確認作業に向いています。
条件入力欄には、元の見出しと同じ値を選べるようにしておくと、入力ミスを減らせます。データの入力規則でリストを作る、行見出しや列見出しを参照して選択肢にする、といった方法があります。入力候補が整っていると、MATCH関数が値を見つけられないエラーも起きにくくなります。
条件セルを作るときのポイントは次の通りです。
- 行条件と列条件を分けて配置する
- 見出しと同じ表記を選べるようにする
- 結果セルの近くに条件欄を置く
- 空欄のときの表示を決めておく
条件セルが空欄のままだと、数式がエラーになる場合があります。IF関数やIFERROR関数と組み合わせて、未入力のときは空白を返すようにすると、シートを見た人にとって分かりやすくなります。
表の変更に強い形にする
二方向参照は便利ですが、表の構成が変わると参照範囲の見直しが必要になることがあります。特に、行や列を追加する表では、固定範囲のままだと新しいデータが参照対象に入らない場合があります。表を更新する運用なら、範囲の管理方法を先に決めておくと安心です。
元データをテーブルとして設定しておくと、行の追加に対応しやすくなります。列見出しが増える場合は、数式が参照する範囲に新しい列が含まれるか確認します。また、見出し名の変更はMATCH関数に影響するため、既存の数式で使っている名前を変えるときは注意が必要です。
表を保守しやすくするポイントは次の通りです。
- 本体範囲と見出し範囲の位置関係を崩さない
- 見出し名をむやみに変更しない
- 行や列を追加した後に参照範囲を確認する
- 条件セルの候補リストも合わせて更新する
数式を作ったら終わりではなく、表を更新したときに参照が追従しているかを確認することが大切です。業務で使う表ほど、更新後のチェック欄を用意しておくと運用しやすくなります。
まとめ
ExcelのINDEX関数とMATCH関数を組み合わせると、行見出しと列見出しの条件から交点の値を取り出せます。二方向参照では、本体範囲、行見出し、列見出しを分けて考えることが基本です。
条件入力用のセルを用意し、入力規則で候補を選べるようにすると、検索値の表記ゆれを減らせます。表を更新する場合は、範囲や見出しの変更にも注意します。INDEXとMATCHの二方向参照は、表の構造を保ったまま必要な値を取り出すための実務向きの方法です。