Excel INDEX関数とMATCH関数を組み合わせてVLOOKUP関数より高度な検索を行う方法

2020年6月12日

Excelで検索を行うにはVLOOKUP関数が定番です

実はINDEX関数MATCH関数の方が多くの面で優れています。

この記事ではVLOOKUP関数の代わりにINDEX関数MATCH関数の組み合わせで

検索を行う方法と利点・欠点を紹介します。

※2020年1月にXLOOKUP関数が追加されました。

 更に優れているので使用可能な環境ではそちらの使用を推奨します。

検索を行う方法(数式の解説)

下記のように設定します。

=INDEX(表示項目の範囲,MATCH(検索値,検索範囲,0))
INDEXとMATCHで検索する数式のキャプチャ。吹き出しと色つき図形で引数の位置を解説

引数の解説

MATCH関数

引数指定値
引数1:検索値VLOOKUP関数の引数1と同様です。
検索する値を指定します。
引数2:検索範囲VLOOKUP関数の引数2と似ていますが
表全体ではなく検索対象となる1列のみを指定します。
引数3:検索の種類VLOOKUP関数の引数4に似ています。
省略すると罠がある点も同様です。
ここには0(完全一致)を指定します。
ここを省略してしまうと
1(あいまい検索…検索値以下で最大)となり
意図しない結果になる場合があるため、
しっかり0を指定しましょう。

INDEX関数

引数指定値
引数1:表示項目の範囲VLOOKUP関数での引数3に似ていますが、
列番号でなく表示項目となる1列のみを指定します。
引数2:行番号MATCH関数を指定します。
引数3:列番号省略します。指定する場合は0を指定します。
ただし引数1が1列のみの場合、省略可能です。
省略したほうが余計な記述がなく
数式として質が高いので
引数1を1列にして省略しましょう。

VLOOKUP関数との比較

優れている点(メリット)

下記のような強みがあります。

  1. 数式を横(列方向)にコピーしたい場合に強い点
  2. 表示項目が検索範囲の右にある必要がない点
  3. 結果が値ではなく参照である点

数式を横(列方向)にコピーしたい場合に強い点

数式をコピーオートフィルしたときに差が出ます。

VLOOKUP関数で指定する列番号は横にコピーしても変わりませんが、

INDEX関数MATCH関数では$を付けていなければ

1列づつ動いてくれます。(動かし方は絶対参照$で調整可)

これは複数列を表示したい場合、効率と間違いの少なさで大きなメリットになります。

VLOOKUP関数とINDEX関数+MATCH関数で横方向コピーしたときの動きの違いを図解

この例では数式はこのように設定しています。

セル数式
J4=VLOOKUP($I4,$C$4:$F$9,2,FALSE)
J5=INDEX(D$4:D$9,MATCH($I$5,$C$4:$C$9,0))

VLOOKUP関数で同様のことを行うには工夫が必要です。

ただしXLOOKUP関数では列番号でなくセル範囲指定になっているため

その点が改善されておりINDEX関数MATCH関数より有効です。

検索範囲が表示項目の左にある必要がない点

VLOOKUP関数の欠点の一つに

検索値に対応する列は引数2の範囲内で

左端にあることが必須な点があります。

しかしINDEX関数MATCH関数の方法では

この制約がないため名称・名前から番号やコードの取得が容易です。

検索範囲が表示項目の右にある状態の図解

VLOOKUP関数で同様のことを行うには工夫が必要です。

ただしXLOOKUP関数ではその点も改善されており

INDEX関数MATCH関数より有効です。

結果が値ではなくセル参照である点

活きる機会は少ないメリットですが、結果の返し方が異なります。

まずINDEX関数では結果がセル参照です。

INDEX関数では結果がセル参照である状態の図解

そしてVLOOKUP関数では結果が値です。

VLOOKUP関数では結果が値である状態の図解

これがどのような違いを生むかというと結果がセル参照である場合は、

関数の結果をOFFSET関数の引数にしてセルをずらす、

関数を範囲名に使い画像の検索に用いる、といった応用が可能になる点です。

ただしXLOOKUP関数でも結果はセル参照になっており、違いがなくなっています。

劣っている点(デメリット)

下記のような弱みがあります。

(一つ目は比較対象がVLOOKUP関数ではないですが)

  1. XLOOKUP関数が上位互換
  2. 数式が判りづらい点

XLOOKUP関数が上位互換

2020年1月にVLOOKUP関数の上位版のXLOOKUP関数が実装されました。

INDEX関数MATCH関数の利点を取り込んだような関数になっているため

わざわざ関数を組み合わせて数式を複雑にしてまで使う理由が激減します。

(古いバージョンでも使える利点は残りますが)

数式が判りづらい点

VLOOKUP関数より数式が判りにくい点が弱みです。

INDEX関数MATCH関数は知名度が低い上に

複数の関数を組み合わせると、どうしても読みにくくなります。

自分のみが使うブックであれば影響は皆無ですが、

会社で複数人が見るようなブックの場合では

この分かりにくさが劣る点になってしまいます。

よって可能な限りVLOOKUP関数を使用し

必要な時のみINDEX関数MATCH関数

使用することが現実的な運用になります。

関連記事

VLOOKUP関数の結果を1行上下にずらす方法

XLOOKUP(VLOOKUP)関数の使い方・活用方法の記事一覧