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

indexとmatchでvlookupの一歩先へWEB関連

Excel大好きプログラミングOLこと、くらげ(@owasa7)です😃

Excel関数のなかで人気高いのVLOOKUP関数ですが、使用していて若干使いづらいなと感じる場面ってありませんか?

いつだって返してほしい値が右側にあるとは限らないし、列数の指定もめんどくさい💦

今回の記事では、VLOOKUPを使わずにさらに高度な検索ができるINDEX関数MATCH関数を紹介します。

この記事で紹介する関数:

  • VLOOKUP: 検索値を定められた範囲の左端から探し、指定された列から対応する値を返す。
  • INDEX: 定められた範囲の中で、指定された行・列のセルの値を返す。
  • MATCH: 指定した範囲の中で、検索値のセルの場所を返す。

VLOOKUPのおさらい!

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

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

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

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

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

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

VLOOKUP関数の使い方

上記の場合は、商品名からNoを検索しようとしています。

しかし検索値である「バナナ」よりも「2」が左にあるため、VLOOKUPでは値を返すことができまん。

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

INDEX関数とMATCH関数を組み合わせよう

そこで、使える関数がINDEXMATCHです。

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

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

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

INDEX関数の仕組み

INDEX関数の使い方
+INDEX(配列行番号列番号)
数式+INDEX(B4:C18,2,1)
配列範囲を指定する
行番号返してほしい値の行の番号を入力
列番号返してほしい値の列の番号を入力

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

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

MATCH関数の仕組み

続いては、MATCH関数です。

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

MATCHは検索したいセルが指定した範囲の中で、どの行or列にいるのかを返してくれます。

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

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

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

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

    *照合の種類:
  • 1:検索値以下で最大の値を返す。検索範囲を昇順(小さい順)にしておく必要がある。
  • 0: 完全一致。
  • -1: 検索値以上で最大の値を返す。検索範囲を降順(大きい順)にしておく必要がある。
スポンサーリンク

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をコピーしました