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

2019年12月3日

エクセルで検索を行うには

VLOOKUP関数が定番ですが実は

INDEX関数&MATCH関数の方が

多くの面で優れています。

この記事ではVLOOKUP関数の代わりに

INDEX関数とMATCH関数の組み合わせで

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

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

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

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関数の欠点の一つに

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

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

しかしINDEXとMATCHの方法では

この制約がないため名称・名前から

番号やコードの取得が容易です。

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

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

活きる機会は少ないメリットですが、

結果の返し方が異なります。

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

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

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

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

これがどのような違いを生むかというと

結果がセル参照である場合は、

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

関数を範囲名に使い画像の検索に用いる、

といった応用が可能になる点です。

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

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

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

  1. XLOOKUP関数が実装されると不要になる点
  2. 数式が判りづらい点

XLOOKUP関数がリリースされると不要になる点

究極のデメリットです。

2019年中の予定でVLOOKUP関数の上位版の

XLOOKUP関数が実装されます。

INDEX関数&MATCH関数の利点を

取り込んだような関数になっているため

わざわざ関数を組み合わせて

使う理由がなくなります。

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

数式が判りづらい点

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

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

複数の関数を組み合わせると、

どうしても読みにくくなります。

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

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

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

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

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

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

関連記事

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

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

指定した範囲の行と列に対応する値を取得(INDEX関数)

検索値に対応するセルの相対的な位置を取得(MATCH関数)