VLOOKUP関数とは別の検索方法である、INDEX関数とMATCH関数の使い方、その組み合わせ方、同じ検索関数であるVLOOKUP関数との差を比較してみます。
INDEX関数、MATCH関数の数式
INDEX関数とMATCH関数の組み合わせ
VLOOKUP関数との違い
VLOOKUP関数に関してはこちらをご覧ください。VLOOKUP関数との違いの項目でもお話ししますが、関数は見やすい形のほうが修正がしやすいので両方の使い方を覚えておくほうがいいです。
INDEX関数、MATCH関数の数式
INDEX関数とは？
INDEX関数は、範囲内の縦いくつ、横いくつの位置にあるセルの値を調べる関数です。
=INDEX(範囲,縦位置,横位置)
具体例で見てみましょう。
上の表を使ってINDEXを解説していきます。INDEX関数には関数式にはないですが、『基準点』という考えがあるとスムーズに理解が出来ます。
上の表では赤枠が「範囲」に該当します。その中の青枠が『基準点』になり、基準点から縦(↓)に○セル、横(→)に○セル分を移動した所のセルの値を返します。
試しに、縦に2セル、横に2セル移動したという仮定で考えてみます。
=INDEX(B2:D4,2,2)
この数式の答えは『ぶどう』になります。なお基準点(今回はりんご)を指定するには(範囲,1,1)なります。
MATCH関数とは？
MATCH関数とは、指定したセル範囲の中で目的の値を検索して相対的な位置(値)を返します。
=MATCH(検索値,検索範囲,[照合の範囲])
これも具体例で見てみます。
赤枠の範囲は、=MATCH(検索値,検索範囲,[照合の範囲])を置き換えると、
=MATCH(B6,B1:D1,0)
検索値は『高い』なので、範囲のスタートはINDEX関数と同じ基準点の考え方でいくと『3』列目に該当します。
青枠の範囲は、=MATCH(検索値,検索範囲,[照合の範囲])を置き換えると、
=MATCH(B9,A2:A4,0)
検索値は『果物』なので、範囲のスタートはINDEX関数と同じ基準点の考え方でいくと『3』行目に該当します。
豆知識；行(列)ごと範囲に指定することも可能
数式はA列ならば(A:A) 、1行目ならば(1:1)という形に指定してしまいます。
例えば社員一覧でA列に氏名を追加するとして、人数がどんどん増えていくと都度範囲の幅を変える必要が出てしまう為、列ごと選択してしまうとA列自体を範囲として扱う事が出来ます。
INDEX関数とMATCH関数の組み合わせ
INDEXとMATCHを組み合わせることで、VLOOKUP関数と同様の効果を出すことも可能です。さきほどの表を使ってやってみましょう。
検索条件として考えられるキーワードが『高い』『果物』になります。なのでそれに該当する種類を表の中から選択するという意味になりますね。あとは関数を1つずつ分解して考えてみれば簡単です。
①の座標から1つ目のMATCH関数で取得した4列目へ横移動、2つ目のMATCH関数で取得した4行目に移動した値をINDEXで出したという形です。
VLOOKUP関数との違い
「でも検索ならVLOOKUP関数があるじゃん」という話なんですが、双方にメリットがあります。
VLOOKUP関数のメリットとデメリット
メリット
- 関数式自体が短く済む
- 使用できる人が多い(教えやすい)
デメリット
- 表の形状によって使えない形がある
VLOOKUPの最大の弱点は、検索値の左側からは情報を取り出せないという点です。具体例で見てみます。
こちらの表はVLOOKUP関数と、INDEX関数+MATCH関数でそれぞれ『コード』を検索値としたものです。
=VLOOKUP(G16,$A$17:$C$25,2,0) →『レタス』
=INDEX($A$17:$C$25,MATCH(G16,$A$17:$A$25,0),2) →『レタス』
それぞれ問題ありません。しかし次の表を見るとわかります。
=VLOOKUP(G29,$A$30:$C$38,2,0) →＃N/A
=INDEX($A$30:$C$38,MATCH($G29,$C$30:$C$38,0),2) →モモ
VLOOKUP関数だけエラーになっています。これが左側にある情報は取り出せないという事です。もちろん、列の値を『-(マイナス)』にしても不可能です。
まとめると以下のようになっています。
こう聞くと、「じゃあVLOOKUP関数使わなくていいじゃん」って話になりそうですが、INDEX関数+MATCH関数にも弱点はあります。
INDEX関数+MATCH関数のメリットとデメリット
メリット
- VLOOKUP関数が出来ない左側からも情報が引き出せる
- OFFSET関数と組み合わせることで、更なる力を発揮する
デメリット
- とにかく関数式が長くなるので、あとから修正するときが大変
さきほどの表で、VLOOKUP関数が出来ていなかった下のパターンでもINDEX関数+MATCH関数では結果を表示できています。
また、OFFSET関数というものがあります。
=OFFSET(基準となるセル,基準からいくつ縦にずれるか, 基準からいくつ横にずれるか,範囲の行数,範囲の列数)
詳しくは別の回でお話ししますが、簡単に言ってしまえばINDEX関数+MATCH関数と組み合わせることで『検索条件に合うセル範囲内で自動計算』なんてことも可能になります。
VLOOKUP関数はあくまで単一のセルの値しか返せないので、この差は大きいです。
ただ、デメリットはやはり「関数式が長くなりがち」ということに尽きます。今回の例題ではIF関数などが入っていないですが、条件定義などが複雑になればなるほど「INDEX(○:○(MATCH(○○～」の数式自体の出現回数が増えます。
状況に応じて使い分けられるように、両方使いこなしておくのがベストです。
コメント
記事の中で「VLOOKUP関数のメリットとデメリット」項目が２回表記されていますよ。
しかし本件の内容については画像を多用している為イメージしやすく、とても分かりやすかったです。
ご連絡ありがとうございます。
２つ目の「VLOOKUP関数のメリットとデメリット」は正しくは「INDEX関数+MATCH関数のメリットとデメリット」でした。
修正をさせていただきました。
記事内容につきましても重ね重ねありがとうございます。