Excelでよく使われる関数といえばVLOOKUP。経理の業務では日常的に使われる関数ですが、使っていく中で「左側の列が参照できない」、「列の追加で参照先がおかしくなった」「処理が重くなった」と感じたことはありませんか？
その悩み、実はINDEX+MATCH関数でスマートに解決できます。今回はVLOOKUPの限界を整理しつつ、より柔軟に使えるINDEX+MATCHの活用法をご紹介します。
VLOOKUP関数の弱点と限界
左側参照ができない
VLOOKUP関数では参照するデータの範囲の一番左のセルを起点として、何番目のセルの値を返すというものです。
つまり、起点よりも左側の値を返すことはできないのです。
次の「架空の従業員マスタのサンプルデータ」を例にとってみます。
VLOOKUP関数の場合、一般的には従業員IDがE001のセルを起点に範囲指定して、指定した列のセルの値を返します。よって、従業員IDが検索値となります。
例えば、従業員IDがE008の人の入社日を出力したい場合、次の図のようにVLOOKUP関数を入力することで得られます。
この時、参照範囲の一番左の列から検索値を指定して、検索値を一番目として右に何番目の列の番号の値を返すかという式になります。
つまり、検索値から左側の列の番号の値を返すを返すことができないのです。例えば、上記従業員マスタで、「加藤 大地」の従業員IDを求めたい場合、VLOOKUP関数では氏名から求めることはできないのです。
そもそも左側参照ができないことで何が困るのか？
実務では従業員IDや商品コードを覚えず、氏名や商品名を直接入力するケースがほとんどです。
VLOOKUPだけでは、以下のような不便が生じます。
- 入力担当者が別途IDを調べる手間が増える
- ID入力ミスによる計算エラーや不一致が発生しやすい
- 帳票構造が変わるたびに数式を修正しなければならない
しかし、INDEX＋MATCH関数では左側検索もできるので、そのような悩みも解消されます。
詳しい使い方は後程解説しますが、下の図のように検索値の左側の値もバッチリ返すことができます。
列の追加・並べ替えで壊れやすい
VLOOKUP関数の場合、VOOKUP(検索値, 範囲, 列番号, [検索方法])の構成になっており、列番号を数値のまま指定すると参照範囲に列を追加すると式が壊れてしまいます。
例えば、下の図のように、先ほどの従業員マスタの部署の列(7番目)を参照しているVLOOKUP関数を使用した式があるとします。そこで、従業員マスタの4番目と5番目の列の間に「出身地」という列を追加します。そうすると、意図しない列（入社日）を返し、正しく動作しなくなります。
このような問題も、INDEX+MATCH関数でも解決できます。
※ただし、VLOOKUP関数とMATCH関数の組み合わせでも列追加に強くなります。
大量データによるパフォーマンス低下
例えば10万行を超えるような大規模テーブルで、数千セルにVLOOKUP関数を使用すると、Excelは完全一致検索のたびにテーブル全体を先頭から線形探索します。結果として再計算に数十秒～数分要し、業務の足かせになるケースも珍しくありません。
このような問題は、INDEX + MATCH関数の組み合わせで改善できます。
INDEX+MATCHでVLOOKUPの悩みをすべて解決
左側参照ができる理由
INDEX関数の基本構文は
INDEX(参照範囲, 行番号, [列番号])
となっており、参照範囲内の行番号と列番号を指定して対応するセルの値を返します。VLOOKUP関数のように「左端列から何列目」といった制約がないため、検索対象の右側だけでなく、範囲の左側にある値も取り出せます。
行番号（または列番号）を動的に取得するにはMATCH関数を組み合わせます。MATCHで検索値が参照範囲の何行目（または何列目）にあるかを調べ、その結果をINDEXに渡すことで、どの位置にあっても正しく値を返せる仕組みです。
後ほど、INDEXとMATCHを組み合わせた具体的な使い方を解説します。
なぜ列の追加・順序変更に強いのか
INDEX＋MATCH関数では、検索列と取得列を個別に指定できるため、列の挿入や並べ替えを行っても結果がずれません。
- MATCH関数：検索キーを探す列範囲のみを指定
- INDEX関数：取得したい列範囲のみを指定
といったように範囲を分けて指定するため、列が増えたり順序が変わっても、どの列を検索し、どの列を返すかを明示的にコントロールできます。
一方、VLOOKUP関数は第3引数で「参照範囲の先頭から何列目か」を数値指定します。
列を追加すると列番号がずれてしまい、返る値も変わってしまいます。
処理速度が向上する理由
VLOOKUP関数は「テーブル全体（A列〜Z列など）」を範囲指定し、その左端列を線形探索します。
一方、MATCH関数では「検索キーがある列だけ」を指定できるため、列数の多いテーブルでも探索対象を最小化できます。また、MATCH関数で得た行番号をそのままINDEX関数に渡すので、余計なテーブル構造の解析が発生しません。
INDEX+MATCH関数の使い方と実践例
基本構文（INDEX＋MATCHの組み合わせ方）
先ほども解説しましたがINDEX関数の基本構文は、
INDEX(参照範囲, 行番号, [列番号])
となっており、参照範囲内の指定した行番号（と列番号）の位置にある値を返します。
例えば、行番号のみを指定した場合のパターンは下の図の通りになります。
また、MATCH関数の基本構文は、
MATCH(検索値, 検索範囲, [照合の型])
となっており、検索範囲にある検索値が相対位置で何行目(または何列目)にあるのかを返します。
例えば、B2:B11の範囲で加藤 大地の行番号(相対位置)を返したい場合は以下の図のようになります。
以上から、INDEX関数とMATCH関数には次のような特徴があります。
- INDEX関数: 指定範囲の指定した行番号（と列番号）から値を返す
- MATCH関数: 指定範囲から検索値の行番号（または列番号）を返す
よって、これら2つの関数を組み合わせることによって、任意の検索値における任意の情報を取得できる。
具体的な構文は、
INDEX(参照範囲, MATCH(検索値, 検索範囲, [照合の型]), [MATCH(検索値, 検索範囲, [照合の型])])
となります。
例えば、入社日の列をINDEX関数の参照範囲にして、「加藤 大地」をMATCH関数の検索値として、入社日を返す式を作成すると次の図のようになります。
上の図では、まずMATCH関数で「氏名」の列範囲の中から「加藤 大地」を検索し、その行番号（相対位置）の「8」を得ます。
次に、INDEX関数で「入社日」の列範囲の中から、MATCH関数で得られた行番号「8」の加藤 大地の入社日である「2018-06-01」を返しています。
また、応用としてINDEX関数に列番号も追加して2次元的に検索する方法もあります。
第三引数にMATCH関数を組み合わせて、列番号を検索することで様々な応用ができます。
例えば、従業員マスタで「氏名」の列から「加藤 大地」を検索して、見出しの行（1行目）から生年月日の列番号を検索することで、加藤 大地の生年月日を返すことができます。
経理業務での実例
経理業務でINDEX＋MATCH関数を利用する一例を紹介します。
それは、給与計算用の資料です。その中でも住民税の特別徴収額を抽出する場合には、理にかなった使用方法だと思います。
まず、扱うテーブルの一番左の列に従業員ID（または従業員名）を配置します。そして、一番上の行に「4月」、「5月」、「6月」…と配置します。そして、その行と列が交差するところに該当する特別徴収額を入力してテーブルを作成します。
そして、社員の給与からの天引き額をまとめたシート（またはテーブル等）に、INDEX＋MATCH関数を利用した式を入力し、社員からの住民税の特別徴収額を抽出して給与資料を作成します。
仮に次のような住民税の特別徴収額をまとめた一覧があるとします。
この住民税額を別シートの支給・控除額一覧の給与資料に反映させることを想定します。
今回、上図のA列の従業員IDとA1の8月を検索値として、J列に住民税額を返していきます。
例えば、「E001」の「佐藤 太郎」の住民税額を返すINDEX+MATCH関数を使用した式は、
=INDEX(T_住民税!$C$2:$N$11,MATCH($A3,T_住民税!$A$2:$A$11,0),MATCH($A$1,T_住民税!$C$1:$N$1,0))
となります。
INDEX関数の第1引数の「T_住民税!$C$2:$N$11」は、住民税の特別徴収額の一覧のシートの住民税額を参照としています。つまり、「佐藤 太郎」の6月の税額から「松本 拓海」の5月の税額を参照範囲としています。
次に、INDEX関数の第2引数はMATCH関数により行番号を返しています。この第2引数のMATCH関数はA2の従業員ID「E001」を検索値として、T_住民税のシートのE001からE010の行番号（相対位置）の1を返しています。
また、INDEX関数の第3引数はMATCH関数により列番号を返しています。この第3引数のMATCH関数はA1の「8月」という文字列を検索値として、T_住民税のシートの6月から5月の列番号（相対位置）の3を返しています。
したがって、式全体としてはT_住民税のシートの「佐藤 太郎」の6月の税額から「松本 拓海」の5月の税額の参照範囲で1行3列目の25,000を返すようになっています。
また、住民税の特別徴収額に限らず、社会保険料の控除額等も月毎の一覧表から従業員IDと該当月から必要な数値を持ってこれるので非常に便利です。
まとめ
経理業務ではExcelでのデータ抽出において、VLOOKUP関数は幅広く使われていますが、INDEX＋MATCH関数の方が拡張性や柔軟性の面で優れています。
以下の点を意識すると、実務での使いやすさが一段と増します。
- 左右の制限なし: VLOOKUP関数は検索列が必ず一番左にある必要がありますが、INDEX＋MATCH関数はどの列でも検索可能であるため、レイアウトの自由度が高まります。
- 列の追加に強い: VLOOKUP関数は列の挿入・削除で列番号がズレるとエラーの元になりますが、MATCH関数で列位置を動的に指定するINDEX＋MATCH関数なら、構造変更にも柔軟に対応可能。
- 処理速度にも優位性あり: 大量データの処理や高度な検索において、INDEX＋MATCH関数は効率的な参照が可能。実務でのパフォーマンス改善にも貢献します。
- 複数条件にも応用可能: 複雑な条件検索や横断的なデータ抽出でも、組み合わせ次第で柔軟に対応できるのがINDEX＋MATCH関数の強み。
VLOOKUP関数は初心者にとって取り組みやすい一方、業務の効率化を目指すならINDEX＋MATCH関数の使いこなしが鍵になります。特に給与計算や控除データなど、構造が複雑になりがちな経理資料では、その真価を発揮します。
コメント