bibou: さらば、VLOOKUP関数

エクセルにあたり避けて通れないVLOOKUP関数だが、個人的には不満があった。
その不満というのは、

「左から右に検索するだけじゃなく、右から左にも検索したい。。」

というもの。
その問題について、ひとつ解決策を考えてみた...

VLOOKUP関数の限界

Vlookup関数は、その構造上、検索したい値は常に検索範囲の左端になくてはならない。「出席番号2番の人の点数は何点かな？」ということを知りたい場合は、下図Aの通り、検索値たる出席番号を検索範囲の一番左にもってこなくてはならない。

図A:

もし下図Bのように、出席番号が右側にあったら、VLOOKUPは機能しない。

図B：

でも、仕事では、わりと多くの局面で

「表の真ん中くらいにある列のデータを検索値として、そこから左にあるデータを検索したいな...」

と歯ぎしりすることが多い。

左を検索したいということで、無邪気に

VLOOKUP（検索値、データ範囲、-2）

みたいに負の値を入れてみても、VLOOKUP関数の構造の限界により、単にエラーが出力される。

そんなときのために、INDEX関数とMATCH関数を組み合わせることで、検索値を左端以外においてもワークするやり方が以下。

例えば図Bにおいて、出席番号3の人の点数を検索したいときは

INDEX(A1:C4, MATCH(C1:C4,3), 2)

とすればよい。

この関数を無理矢理日本語にすると、

・まずC1:C4で、出席番号3が何行目にあるか調べよ→4行目でした
・次に、A1:C4というデータ範囲において、（X座標、Y座標）＝（出席番号3の行数、２）、すなわち（X,Y）=（4,2）のデータを出力せよ→39点でした

といった感じになる。これを使えば、

（１）検索値をデータ範囲の左端に置かなくてもよくなる
（２）検索値がある行を、データ範囲に含めなくても検索できてしまう

という2つのメリットがある。この2つのメリットは、数式が若干複雑になることを考慮しても余りあるメリットと言えるので、今後はよっぽどシンプルな状況でない限りは基本的にINDEXとMATCHの組み合わせで検索していくのが良いかと思っている。

※なお、MATCHで行ではなく列を調べることで、VLOOKUP同様にHLOOKUPのイミテーションも可能

・この水準のマニア度のExcel小ネタが一番詳しいのはExcel Hacks(Link)。これは手元にぜひ一冊おいておきたい。
・一段素朴なレベルだが、「資料作成」「他人との共有」という観点では、この本(→リンク)がとっても親切。