Excel大好きプログラミングOLこと、くらげ(@owasa7)です😃
Excel関数のなかで人気高いのVLOOKUP関数ですが、使用していて若干使いづらいなと感じる場面ってありませんか？
いつだって返してほしい値が右側にあるとは限らないし、列数の指定もめんどくさい💦
今回の記事では、VLOOKUPを使わずにさらに高度な検索ができるINDEX関数とMATCH関数を紹介します。
この記事で紹介する関数：
- VLOOKUP: 検索値を定められた範囲の左端から探し、指定された列から対応する値を返す。
- INDEX: 定められた範囲の中で、指定された行・列のセルの値を返す。
- MATCH: 指定した範囲の中で、検索値のセルの場所を返す。
VLOOKUPのおさらい！
意外と忘れているかもしれないので、VLOOKUPのおさらいをしていきましょう。
VLOOKUPとは、左端の検索値に対応する値を、定められた範囲中から返すという関数です。
例えば番号(No)と商品名が対応している下記表では、「2」を検索するとその右隣りの「バナナ」を返します。
数式 | +VLOOKUP(E4, B:C, 2, 0) |
検索値 (E4) | 検索の基準となる値 |
範囲 (B:C) | 検索する範囲 |
列番号 (2) | 返してほしい値が、指定した範囲で左から何列目にあるか |
検索方法 (0) | ０（完全一致）もしくは１（近似値）を入力 |
VLOOKUPは自身を左端とし、そこから与えられた列番号分右に検索をします。
そのため、返してほしい値が検索値より左にある場合は、エラーとなります。
上記の場合は、商品名からNoを検索しようとしています。
しかし検索値である「バナナ」よりも「2」が左にあるため、VLOOKUPでは値を返すことができまん。
(No列をD列にコピペしてVLOOKUPすることもできますが・・😅)
INDEX関数とMATCH関数を組み合わせよう
そこで、使える関数がINDEXとMATCHです。
INDEXとMATCHの二つの関数を使うことで、「バナナ行のNo列の値を返しなさい」という数式を作っているという仕組みです。
それぞれの関数がどう作用しているのか、説明します。
INDEX関数の仕組み
| 数式 | +INDEX(B4:C18,2,1) |
| 配列 | 範囲を指定する |
| 行番号 | 返してほしい値の行の番号を入力 |
| 列番号 | 返してほしい値の列の番号を入力 |
INDEX関数は、行と列数を指定してあげると範囲内の値を返してくれます。
なので、「バナナ」に対応している「2」を返してほしい場合は、2行・1列目を入力すれば、その値が表示されます。
MATCH関数の仕組み
続いては、MATCH関数です。
| 数式 | +MATCH(C4, C4:C18, 0) |
| 検索値 | 検索の基準となる値 |
| 検索範囲 | 行数を返してほしい場合は列を、列数を返してほしい場合は行を範囲にする |
| 照合の種類 | 1, 0, -1を入力＊、indexとの組み合わせの場合は、0を置く |
MATCHは検索したいセルが指定した範囲の中で、どの行or列にいるのかを返してくれます。
列を返してほしい場合は、下記の通り検索範囲を行にします。
ちょっとややこしいですが、単独で使うことはあまりないと思うので、
ここでは概念だけ理解いただければ大丈夫です！
- ＊照合の種類:
- 1：検索値以下で最大の値を返す。検索範囲を昇順(小さい順)にしておく必要がある。
- 0: 完全一致。
- -1: 検索値以上で最大の値を返す。検索範囲を降順(大きい順)にしておく必要がある。
INDEX関数とMATCH関数の使い方
あらためて、INDEXとMATCHを組み合わせた数式を見てみましょう。
| 数式 | +INDEX(B4:C18, MATCH(F5,C4:C18,0),MATCH(E4,B3:C3,0)) |
| 配列 | 番号と商品名の値が入っているところだけを指定 |
| 検索値 | 行で検索してほしい値を指定、今回は”バナナ” |
| 検索範囲 | バナナが入っている列を範囲に指定 |
| 照合の種類 | 完全一致の0を入力 |
| 検索値 | 列で探してほしい値を指定、今回は”No” |
| 検索範囲 | Noが入っている行を範囲に指定 |
| 照合の種類 | 完全一致の0を入力 |
こうすることで、MATCH関数で行・列数を検索し、INDEX関数で該当するセルの値を返すことができます。
行と列がややこしいので、理解が難しいのが難点ですが・・・。
イメージをつかめれば、VLOOKUPで使用できなかったより高度な検索が可能となります！
