INDEX関数とMATCH関数を組み合わせて、VLOOKUPの一歩先へ

indexとmatchでvlookupの一歩先へ Excel

Excel関数のなかで最も人気のVLOOKUPですが、使用していて若干使いづらいなと感じる場面があります。

そこで、VLOOKUP以外でも同じ検索ができるINDEX関数とMATCH関数を紹介します。

VLOOKUPのおさらい!

意外と忘れているかもしれないので、VLOOKUPのおさらいをしていきましょう。

VLOOKUPとは、左端の検索値に対応する値を、定められた範囲中から返すという関数です。

例えば番号と商品名が対応している下記表では、2を検索するとその右隣りのバナナを返します。

VLOOKUP関数の使い方
★VLOOKUP: 検索値を定められた範囲の左端から探し、指定された列から対応する値を返す。
+VLOOKUP(検索値範囲列番号検索方法)
数式
+VLOOKUP(E4, B:C, 2, 0)
検索値 (E4)
検索の基準となる値
範囲 (B:C)
検索する範囲
列番号 (2)
返してほしい値が、指定した範囲で左から何列目にあるか
検索方法 (0)
0(完全一致)もしくは1(近似値)を入力

VLOOKUPは自身を左端とし、そこから与えられた列番号分右に検索をします。

そのため、返してほしい値が検索値より左にある場合は、エラーとなります。

VLOOKUP関数の使い方

上記の場合は、検索値である商品名よりもNoが左にあるため、VLOOKUPでは値を返すことができまん。

(No列をD列にコピペしてVLOOKUPすることもできますが・・😅)

INDEX関数とMATCH関数を使って、VLOOKUPを超えよう!

そこで、使える関数がINDEXとMATCHです。

INDEX関数・MATCH関数の組み合わせ
二つの関数を組み合わせます。

INDEXとMATCHの二つの関数を使うことで、「バナナ行のNo列の値を返しなさい」という数式を作っているという仕組みです。

それぞれの関数がどう作用しているのかを、説明します。

INDEX関数の仕組み

INDEX関数の使い方
★INDEX:定められた範囲の中で、指定された行・列のセルの値を返す。
+INDEX(配列行番号列番号)
数式+INDEX(B4:C18,2,1)
配列範囲を指定する
行番号返してほしい値の行の番号を入力
列番号返してほしい値の列の番号を入力

INDEX関数を使えば、行と列数を指定してあげると範囲内の値を返してくれます。

なので、バナナに対応しているNo2を返してほしい場合は、2行1列目を入力すれば、その値が表示されます。

MATCH関数の仕組み

続いては、MATCH関数です。

★MATCH: 指定した範囲の中で、検索値のセルの場所を返す。

MATCH関数の使い方
りんごは何行目?
+MATCH(検索値検索範囲照合の種類)
数式+MATCH(C4, C4:C18, 0)
検索値検索の基準となる値
検索範囲行数を返してほしい場合は列を、列数を返してほしい場合は行を範囲にする
照合の種類1, 0, -1を入力*、indexとの組み合わせの場合は、0を置く

照合の種類: 

1:検索値以下で最大の値を返す。検索範囲を昇順(小さい順)にしておく必要がある。

0: 完全一致。

-1: 検索値以上で最大の値を返す。検索範囲を降順(大きい順)にしておく必要がある。

列を返してほしい場合は、下記の通り検索範囲を行にします。

MATCH関数の使い方
りんごは何列目?

ちょっとややこしいですが、単独で使うことはあまりないと思うので、

ここでは概念だけ理解いただければ大丈夫です!

INDEX関数とMATCH関数の使い方、まとめ

あらためて、INDEXとMATCHを組み合わせた数式を見てみましょう。

INDEX関数・MATCH関数の組み合わせ
+INDEX(配列, MATCH(検索値,検索範囲,照合の種類),MATCH(検索値, 検索の範囲, 照合の種類))
数式+INDEX(B4:C18, MATCH(F5,C4:C18,0),MATCH(E4,B3:C3,0))
配列番号と商品名の値が入っているところだけを指定
検索値行で検索してほしい値を指定、今回は”バナナ”
検索範囲バナナが入っている列を範囲に指定
照合の種類完全一致の0を入力
検索値列で探してほしい値を指定、今回は”No”
検索範囲Noが入っている行を範囲に指定
照合の種類完全一致の0を入力

こうすることで、MATCH関数で行・列数を検索し、INDEX関数で該当するセルの値を返すことができます!

行と列がややこしいので、理解が難しいのが難点ですが・・・。

イメージをつかめれば、VLOOKUPで使用できなかったより高度な検索が可能となります!

タイトルとURLをコピーしました