vlookupの代替！index＆matchを使って前の列を検索する【Excel／スプレッドシート】

今回は、
・vlookup関数を組んだけど、他の人が入力をしていくと壊れてしまう…
・vlookup関数よりもっと柔軟に検索ができる方法を知りたい
という悩みに答えていきます。

紹介するのは、
INDEX関数とMATCH関数を組み合わせた検索方法です。
この組み合わせを使うことでvlookupと同じ検索をより柔軟に、変化に強い検索ができるようになります。

indexとmatch関数を使って高度に検索をする

まずはどのようにすれば答えが出せるかを例題を通して解説をしていきたいと思います。
実際の解説しているファイルは公開しています。ファイルと見比べながら見ていきたい人はこちらからみてみてください。

例題：IDに一致する性・名・出身地を出力する

最終的な出力結果はこちらになります。

具体的にG3セルに入力されている関数を例に解説していきます。

=INDEX($A:$D,MATCH($F3,$A:$A,0),MATCH(G$2,$A$1:$D$1,0))

一つ一つ分解をしていきます。
まず、全体を囲っているindex関数。

index関数は指定の範囲に対して、その範囲の一番左上を起点として、
列、行を指定の数だけ移動し、その場所にかかれているデータを出力する関数です。
今回やりたい処理は、指定したIDを指定の列から探し出して、出力するため

index（検索範囲 , 指定したIDがある行 , 指定した列番号）

と書くことで指定のデータを取り出すことができます。

今回の検索範囲は、IDも含まれ、最終的に出したいデータも含まれるA列〜D列なので、
A：Dと指定します。
第2引数と第3引数に入る関数を解説していきます。

match関数を使ってIDが含まれる行番号を検索する

index関数の第２引数では、何行分移動してほしいか、を指定します。
今回は、指定のIDの行番号分だけ移動してほしいため、
検索範囲から一致したものの番号をだしてくれるmatch関数を使っていきます。

match関数は、

match（検索値 , 検索範囲 , 検索方法）

となります。
指定のIDの行番号をだしたいので、
検索値＝探したいID番号＝F3セル
検索範囲＝A列
検索方法＝完全一致＝０　※完全一致はmatch関数では0を指定します。

となり、

=MATCH（ F3 , A:A , 0 ）

と関数を書くことができます。
後ほど関数をコピーしていくことになるので、参照を少し工夫しておきましょう。

=MATCH（ $F3 , $A:$A , 0 ）

これで指定のIDの行番号をだすことができました。
matchだけで出力をしてみると「3」という値が出力され、検索IDが17402はしっかり3行目にいることが確認できます。

match関数を使って指定の列番号を検索する

次に、指定の列番号を出力していきましょう。
今回の例題では、性・名・出身地を出力したいため、
関数を使って、２・３・４と出力できればOKです。
行番号を出すときと基本的な流れは同じです。
まずは性から出力します。

=MATCH（ G$2 , $A$1:$D$1 , 0 ）

このように書くことで、
”性”というワードをA1〜D1の範囲から完全一致で探し出して、その番号を出力して下さい
という指示になるので、「2」という結果を得ることができます。

index関数に組み込む

ここまでできたら、最後はindex関数に入れ込めば完成です。
（上記で作った関数をコピペでindex関数内にいれていきましょう。）

=INDEX($A:$D,MATCH($F3,$A:$A,0),MATCH(G$2,$A$1:$D$1,0))

これでindex関数とmatch関数を使ってvlookupと同じような検索をすることができました。
あとは、名・出身地も追加してあげれば完成です。
横にコピーをして伸ばしてみましょう。

これで今回の例題をクリアすることができました。

vlookup関数とindexとmatch関数の組み合わせの違い

次に、vlookup関数とindexとmatch関数を使った指定のメリット・デメリットを解説していきます。

	メリット	デメリット
vlookup	関数がシンプル／理解できる人が多く、運用しやすい	列が増えたとき等イレギュラーがあるときに関数が対応できない
indexとmatch	列が増えても入れ替わっても対応できる／検索値が最左でなくても検索できる	関数が複雑

indexとmatch関数は列が増える、入れ替わっても対応できる

今回紹介したindexとmatchでは、vlookupと比較すると大きなメリットが2つあります。

①列が入れ替わるor増減しても関数を更新する必要がない
②検索値が最左になくても検索できる

列が増減してもエラーがおきない

例えば、下の画像の通り関数を組んだとしましょう。
vlookupとindexとmatch関数で同じような出力結果が得られます。

ある時、データが増えて、列を追加することになりました。
そうして列を追加してみましょう。
そうするとvlookupの関数は値を出力してしまいました。

これは、vlookup関数の第三引数で列を「3」と指定しており、列が増えている場合、変動に対応できないためです。
一方match関数では、「名」という列名を検索しているため、列が増えても対応できます。
これはデータが高頻度で変動する場合はとても大きなメリットになります。
※列名で検索しているため、同じ列名が出現する場合はエラーとなります。一意の列名になるようにしましょう。

検索値が最左になくても検索できる

場合によっては、検索値が最左列に指定ができない場面がありますが、その場合vlookup関数では対応ができません。
（関数の性質上範囲の最左のみが検索範囲として指定できるため）

match関数の場合は、検索する列を指定することができます。

列の増減と同じく、検索したいデータが頻繁に変わったりする場合は、indexとmatch関数を使うことが好ましいです。
一方で、index関数とmatch関数で書いていくとどうしても関数が複雑になりがちです。
そのため、複数人で運用するときに、運用がしにくかったり、改良をしようとなったときに時間がかかりやすくなるデメリットもあります。
便利には違いないのですが、場合によって適切な関数を選んでいくようにしましょう。