VLOOKUP変換ツール｜NO MORE VLOOKUP PROJECT

プロジェクトの概要

はじめに
求人市場においてVLOOKUPの需要は非常に高い

❝VLOOKUPは使えますか？❞

私はこの質問を過去に２度受けたことがある。何れも企業面接を受けたときに面接官が発した言葉であった。どうやら彼らの中では、「VLOOKUPが使える＝EXCEL（スプレッドシート）がある程度使える」という定義がされているようで、実際に世の中の求人広告には「VLOOKUPが使える」人を求む案件が数多く存在する。

indeedでキーワード「VLOOKUP」での検索結果の件数は、EXCEL（スプレッドシート）関数では関数の王様「SUM」の次に多い。（私調べ）

例えば、あなたがある企業の面接を受けるとしよう。面接官から「VLOOKUPは使えますか？」と質問されたときに次のように述べることができればどうだろうか？

❝はい、使えます。しかしVLOOKUPには２つの大きな欠点があるため、私は使わないようにしています。❞

これであなたはきっと面接官から一目置かれることだろう。（または激しくイラッとされることだろう…諸刃の剣）

VLOOKUPの欠点その１
データ範囲の左端列しか検索できない

仮に、次のようなデータがあるとしよう。

実務では何の参考にもならない帝愛地下王国の例題

H2セルに注文番号を入力すると、VLOOKUPで商品名とペリカは表示できるが、注文者情報はマスターデータ上（B〜E列）で注文番号（C列）の左側（B列）に位置するため、拾うことができない。

VLOOKUP関数の第３引数（ここでは「2」）は、検索対象列（ここでは「C列」）から数えてｘ番目（ここでは「２番目」）の列（ここでは「D列」）を示すので、注文番号10003の場合、VLOOKUP関数の第３引数を「2」にすれば商品名（D列）が、「3」にすればペリカ（E列）が、「1」にすれば自分自身（C列）の値が取得できる。

そうとなれば、この第３引数を１より小さくすれば検索対象列の左側に位置するものも拾えるのでは？と思うのが世の常、人の常。しかし実際に0を入れてみると返ってくるのは虚しくも「#VALUE!」だけ。

前田のはずが…

では、どうすれば良いか？結論を先に述べよう。今回の関数

=VLOOKUP(H2,C3:E7,0,FALSE)

をそのままVLOOKUP変換ツールにかけてみよう。変換される関数は

=INDEX(B3:B7,MATCH(H2,C3:C7,0))

となる。この関数を先程の注文者表示セル（G5）に入れてみるとどうなるか？

前田キターーーーッ!!

これこそが当プロジェクトで激推しする「INDEX＆MATCH組み合わせ型」の全貌である。

ちなみに、「OFFSET＆MATCH組み合わせ型」というのもあって、

=OFFSET(C2,MATCH(H2,C3:C7,0),-1)

にしても結果は同じだが、この「OFFSET＆MATCH組み合わせ型」というのは、次に紹介する「VLOOKUPの欠点その２」をクリアできないため、当プロジェクトではその存在をガン無視で貫く所存である。

VLOOKUPの欠点その２
データ範囲の列が増減する都度、関数を修正しなければならない

今度は、マスターデータの列を一つ増やしてみよう。

まずは、この状態から

▼

列を増やすとこうなる

理由は単純で、VLOOKUP関数の第３引数（ここでは「2」）は、検索対象列（ここでは「C列」）から数えてｘ番目（ここでは「２番目」）の列（ここでは「D列」）を示すものであるため、列を増やしたことで注文番号（C列）から数えて２番目のD列が「商品名」から「注文日」に、３番目のE列は、「ペリカ」から「商品名」に入れ替わってしまったからである。

ズレたデータを正すためには、VLOOKUP関数の第３引数の値を、列が増えた分（または減った分）数えて調整する他ない。（ここでは商品名を「2」から「3」に、ペリカを「3」から「4」に）

つまり、「検索対象列からｘ番目の列」というふうに、絶対値で示すというイケてない仕様であることが全ての元凶なのだ。（しかも、前述の通り0や負の数も使えない）

一方で、我らの「INDEX＆MATCHの組み合わせ型」だとどうなるか？

INDEX＆MATCHの組み合わせ型を使うことで

▼

列を増やしてもズレない！

もちろん、検索対象列の左側の値（注文者）も表示できる。

これが完成形！

このように、INDEX＆MATCHの組み合わせ型を使うことで、VLOOKUP関数の欠点を全てカバーできるのである。

少しだけ肩入れ
必ずしもVLOOKUPが悪というわけではない

ここまで散々VLOOKUPのダメ出しを言っておいて今更肩を持つのはアレだが、実はこんな私もVLOOKUPを使うときがある。それは、未来永劫編集することのない、ポイと使い捨てるデータをサクッと作るときに限る。もちろんその理由は、INDEXとMATCHという２つの関数を組み合わせるより、VLOOKUP関数１つで完結させたほうが直感的にわかりやすいが故に、関数を書き終わるまでの時間が１秒でも短く済むからである。

VLOOKUPがINDEX＆MATCH型に勝る唯一無二の強みは、その「わかりやすさ」にある。

おまけ
HLOOKUPの存在も忘れずに

さて、次のグラフは、VLOOKUPとHLOOKUPの人気度を比較したものである。

HLOOKUPガンバレ！

VLOOKUPとは兄弟関数なのに、ここまで差をつけられたHLOOKUPが不憫でならない。

VLOOKUP変換ツールでは、その名前に反してHLOOKUPにも対応しているので、HLOOKUP関数を実務で使っているというマニアックな人には是非活用していただきたい。