Excel

VLOOKUP脱却のススメ【#Excel #関数 #上級者】

あなたは
Excel
VLOOKUP
使って
いますか。

VLOOKUP
とても
便利な
関数ですが、
少し
気難しく、
丁寧に
扱う
必要のある
関数です。

ここでは、
VLOOKUP
使わずに
より便利に
同等以上
機能を
実現する
方法を
紹介します。

結論

INDEX関数と
MATCH関数を
組み合わせて
使います。

サンプル
データは
こちら。

この
記事を
見ている
間は
別の
ウィンドウで
開いて
常に
横で
見られる
ように
しておくと
いいかも
しれません。

INDEX関数は、
指定した
範囲の
何行目
(あるいは何列目)
かの
値を
取り出す
関数です。

MATCH関数は、
指定した
範囲の
何行目に
検索したい
値が
入っているかを
探す
関数です。

INDEX+MATCH
基本的な
書き方は

=INDEX(C:C,MATCH(E3,B:B,0))

のような
形に
なります。

これを
VLOOKUP
使って
書くと、

=VLOOKUP(E3,B:C,2,0)


なります。

どちらで
書いても、
キー列の
A

対応する
知りたい
情報列の
アルファ

正しく
取得
できている
ことが
分かります。

ここから
先では
INDEX+MATCH
メリットと
デメリットを
説明しますが、
ここまでで
なるほど

思った
あなたは
この先を
読む
必要は
ないかも
しれません。

自分の
知らない
メリットが
あるかも
しれないと
思った
あなたや、
この人は
一体
何を
言っているの
だろうと
思った
あなたは、
ぜひ
この先まで
読んでみて
ください。

メリット

大きく
4つ
あります。

  1. 列を 数える 必要が ない
  2. 列を 移動したり 挿入しても 正しく 動作する
  3. ★手前の 列を 検索 できる
  4. ★別の シートを 検索 できる

1.と
2.は
うまく
関数を
組めば
VLOOKUP
使っても
実現
可能
ですが、
3.と
4.は
VLOOKUPでは
絶対に
実現
不可能
INDEX+MATCH
特有
メリットです。

列を 数える 必要が ない

VLOOKUP
使うには、
指定した
範囲の
何列目
知りたい
情報列が
あるのかを
知らなければ
なりません。

列数の
少ない
データなら
特に
問題
ありませんが、
このくらい

列数が
多い
数え
間違えたりして
手間が
かかります。
ちなみに
この
例では
知りたい
情報列は
指定した
範囲の
10列目
あります。

一方、
INDEX+MATCH
キー列と、
知りたい
情報列を、
それぞれ
引数
指定するので、
知りたい
情報列が
指定した
範囲の
何列目
あるかを
数える
必要は
ありません

列を 移動したり 挿入しても 正しく 動作する

VLOOKUPは、
指定した
範囲の
途中に
列を
追加したり、
知りたい
情報列を
移動したり
すると、
正しい
値を
返すことが
できなく
なります。

この
例では
VLOOKUP
取得した
値が
0
なっています。

先の
数式と
比較します。
上が

挿入
下が
挿入
です。

=VLOOKUP(E3,B:C,2,0)
=VLOOKUP(F3,B:D,2,0)
青字

挿入の
結果
変化した
ところ、
赤字

挿入の
結果
変化して
ほしかったのに
変化しなかった
ところです。


挿入の
結果、
指定された
範囲は
拡張された
(B:C→B:D
ものの、
知りたい
情報列までの
列数が
更新されなかった
22
ため、
値が
正しく
取得できなく
なりました。

一方、
INDEX+MATCHでは、
キー列と
知りたい
情報列が
引数
指定されて
いるので、
列を
挿入しても、
移動しても、
値が
正しく
取得できて
います。

★手前の 列を 検索 できる

VLOOKUP
使う
場合は、
指定された
範囲の
一番
左の

キー列に
なっていることが
必要なので、
このように

キー列より

知りたい
情報列が
ある
場合は、
知りたい
情報列の
値を
取得することが
できません
#N/Aエラーに
なって
しまいます。

INDEX+MATCH
場合は、
キー列も
知りたい
情報列も
引数で
指定して
いるので、
知りたい
情報列の
方が
キー列よりも
左側
あっても、
正しく
値を
取得することが
できます

この場合の数式は

=INDEX(B:B,MATCH(E3,C:C,0))

になります。

直感的に
問題が
起きそうも
ない
ことが
理解できる
かも
しれません。

★別の シートを 検索 できる

VLOOKUP
指定できる
検索範囲は
一つだけ
なので、
キー列と
知りたい
情報列が
異なる
シート
ある
場合、
検索
することが
できません

INDEX+MATCHでは、
キー列と
知りたい
情報列が
異なる
シート
あっても、
情報を
検索する
ことが
問題なく
できます

先ほどまで
Sheet1の
C列から
E列に
あった
セルを
Sheet2に
移動
しましたが、
INDEX+MATCH
正しく
値を
取得できて
います

今回は
知りたい
情報列を
別シートに
移動
しましたが、
キー列を
別シートに
移動しても
当然
問題は
ありません

デメリット

ありません

言いたい
ところ
ですが、
しいて
言えば
1つ
あります。

  1. 読みにくい

読みにくい

唯一にして
最大
デメリット
です。

2つ
関数を
組み合わせて
使うため、
1つ
関数である
VLOOKUP
比べて
可読性
低くなります。

ただし、
他人に
関数を
見せる
必要が
ないので
あれば、
ほぼ
無視
できる
デメリットと
言えます。

まとめ

VLOOKUP
使っていて
今は
特に
困って
いない
という
あなたは、
今は
まだ
そのままの
あなたで
いてください。

でも
いつか
VLOOKUP
使っていて
困った
ことが
あったら、
この
ページで
見たことを
思い出してみて
ください。

VLOOKUP
困っている
あなたは、
すぐに
INDEX+MATCH
使いましょう。

きっと
あなたの
困った

解決
できると
信じて
います。