我が憎しみのVLOOKUP
VLOOKUP関数の呪縛
下のような表（図1-1）が与えられている。連番、書籍のタイトル、ISBNコード、著者と訳者のリストである。B12に入力された数字（連番）に対応するタイトルをD12に表示するように関数を組み込みなさいと言われたら、どうすれば良いだろうか。 |
| 図1-1 |
このような場合に頻繁に用いられるのがVLOOKUP関数だ。下の例のように範囲をA2:B10、検索値にB12セル、列番号に範囲の相対的な位置を設定して利用する（図1-2）。
 |
| 図1-2 [検索方法]は、検索対象が文字列の場合、原則FALSEにしておけば良い。 |
結果は下のようになる。
 |
| 図1-3 |
範囲A2:B10の最も左の列（A列）から、検索値に一致するセルを検索し（A4セル）、該当の行の2列目（B列）の値を表示している（図1-3）。
自由無き関数はただ地に転がる石ころのごとく
しかし、VLOOKUP関数は、範囲の一番左の列しか検査できないため、使える状況は著しく限定されている。求めたい値を含む列の左側に検査の対象がある場合、VLOOKUP関数は使うことができない。上と同じ表を用いて、今度はNo.ではなくISBNからタイトルを特定することを考える（図2-1）。VLOOKUP関数を用いて、B12セルに入力されたISBNに対応する書籍のタイトルをD12セルに表示したい。
 |
| 図2-1 |
ISBNはC列、タイトルがB列だから、検査の対象の列が、範囲B2:C10の一番左の列ではない。そのためVLOOKUP関数はC列を検査の対象にできず、B列から値を探そうとするため、N/Aエラーが発生する（図2-2）。
 |
| 図2-2 |
そもそも列番号に入力すべき値を指定することができない。例えば無理に左側を指定しようとマイナスの値などを入力しても（図2-3）、VLOOKUP関数が検査するのはあくまでB列であるから、エラーが返される。
 |
| 図2-3 |
VLOOKUP関数を使う時は、列の並びに注意する必要がある。上の例では、ISBN列とタイトル列を入れ替えなければならない。簡単な表ならまだしも、複雑な関数を利用しているシートでは関連するセルを全て直さねばならず、非常に不便である。
VLOOKUPの上位互換
たったひとつの冴えたやりかた
下の例のように、INDEX関数とMATCH関数の組み合わせ（図3-1）こそが、VLOOKUP関数の有効な代替になる。そしてその利用の喚起こそが、この投稿の主目的である。 |
| 図3-1 |
これを利用した場合、元のデータを加工することなく、上述の目的は達成できる（図3-2）。
 |
| 図3-2 |
以下ではINDEX関数とMATCH関数の、それぞれの挙動について確認した後、組み合わせた用法について詳細を解説し、その有用性を詳らかにしたい。
とある技術のINDEX
INDEX関数は、引数に指定した複数セルの塊（配列）の中で、左上隅を原点とした相対的な位置によってセルを特定し、その内容を返す関数である。下の例では、配列A2:E10の中で、6行目・4列目のセルを探し、その内容を返すことになる（図4-1）。
 |
| 図4-1 |
指定された配列A2:E10の中で6行目・4列目のセルの内容は“シュムペーター”であり、狙い通りの値が返されていることがわかる（図4-2）。
 |
| 図4-2 |
MATCH売りの少女
MATCH関数は特定の列を検査範囲に指定すると、検査値（セルでも文字列でも可）と一致する相対行数を返す。下の例では、B列の2～10行を対象に、文字列“贈与論”が存在するセルの相対行数を返すことになる（図5-1）。
 |
| 図5-2 [照合の種類]には、検査値が文字列である場合は0を指定しておけば良い。 |
“贈与論”が入力されているB6セルは、検査範囲B2:B10の中で、上から5番目のセルであるから、5が返される（図5-3）。
 |
| 図5-3 |
以上がMATCH関数の最も基本的な機能である。この他にも、検査範囲を列ではなく行にできたり、検査値が数字の場合に最も近い数字を含むセルの位置を返したり、などの使い途があるが、ここでは説明しない。
ふたつならヤレルヤ
INDEX関数もMATCH関数も、単独では非常に地味な関数だが、この2つを組み合わせると、極めて使い勝手の良い関数に変貌する。両者の挙動を踏まえた上で、冒頭の組み合わせた関数を再度見てみたい（図6-1）。まずMATCH関数が、B12セルに入力されている文字列を含むセルを、C列から探し、その行数を返す（この場合は6）。
 |
| 図6-1 |
それを受けてINDEX関数が、配列B2:C10の中で、6行目・1列目のセルを特定し、その値である“租税国家の危機”が返される（図6-2）。B12セルに入力されたISBNに対応する書籍のタイトルを表示することに成功している。
|
| 図6-2 |
同じ表を使って、もう一例試してみたい。次は訳者（E列）から、対応するタイトル（B列）の値を求めることにする。
INDEX関数の引数として配列B2：E10を指定し、タイトルは最も左側の列にあるから、列番号には1を指定する。また、MATCH関数の引数には、訳者の含まれるE2:E10を検査範囲に指定し、検査値としてB12セルを指定しておく（図6-3）。
 |
| 図6-3 |
MATCH関数が、文字列“日高 六郎”を含むセルの相対行数である4を返し、INDEX関数が配列の中で4行目・1列目の値である“自由からの逃走”を返し、望んだ結果になることがわかる（図6-4）。
 |
| 図6-4 |
このように、VLOOKUP関数と違い、検索値と抽出したい行との関係は自由自在である。INDEX関数とMATCH関数を組み合わせた用法の有用性がおわかり頂けただろうか。
一見複雑に見えるが、順を追って理解すればVLOOKUP関数よりわかりやすく、2～3回も使ってみればセルに打ち込む時間も殆ど掛からなくなるはずだ。
MATCH一行 齟齬の元
INDEX関数とMATCH関数との組み合わせに限らず、複数の関数を組み合わせる際には、引数の範囲が噛み合っているかどうかについて、注意しておく必要がある。
MATCH関数が返す値も、INDEX関数の引数に設定するのも、その関数にとっての相対的な値であることを忘れてはならない。起こりがちな誤りは、MATCH関数とINDEX関数とで、異なる数の行を引数に設定してしまうことである。
下の例ではINDEX関数がB1:E10の10行を配列にしており、MATCH関数がE2:E10の9行を検査範囲にしている（図7-1）。そのため。例えばE5セルの相対行数が、INDEX関数にとっては5行目で、MATCH関数にとっては4行目ということになってしまう。
 |
| 図7-1 |
MATCH関数は文字列“日高 六郎”が含まれるE5セルを見つけ、相対的な行数である4を返すため、INDEX関数は配列B1:E10の中から4行目・1列目、すなわちB4セルの値を返してしまい、正しい結果が得られない（図7-2）。
 |
| 図7-2 |
上のようなズレを防ぐために、MATCH関数とINDEX関数の変数に、列そのものを指定してしまうと良い。下ではMATCH関数の検査範囲として、E列全て（E:E）を指定している。また、INDEX関数の配列として、A～E列の全て（A:E）を指定している（図8-1）。
 |
| 図7-3 |
これにより、2つの関数の相対行数が確実に一致するので、齟齬が生じることはない。
 |
| 図7-4 |
一般的にExcelで上のような一覧を作る場合、新たに行が追加されることを想定してシートを作成するはずである。今回は説明のために作成したが、図1-1から図7-2までのように、ある列に別の項目が登場してしまうようなシートは望ましくない。
本来であれば図7-3や図7-4のように、1列には1項目のみ（著者の行には著者のみ）存在するように作成すべきである。そうすれば、後から行の追加や挿入があったとしても、上で作成した関数は そのまま使うことができるし、その他の保守性も高まる。
さらばVLOOKUP
ソビエトロシアでは関数が人を使う
以上のように、柔軟な上位互換が存在するにも関わらず、非常に多くの人がその存在を知らず、使いにくいVLOOKUP関数を使い続けている。そして今日も世界のどこかで「VLOOKUP関数は便利だ」と後輩に教える人がいて、また別のどこかでVLOOKUP関数のために列の入れ替え処理をしている人がいる。
果ては「VLOOKUP関数が使いやすいようにファイルを作れ」などという考え方まで標榜される始末である。まさしく人間が道具に使われる典型例ではないか。
関数のために働くのはもうやめよう。
こんな関数はもう要らないんだ
上位互換であるINDEX&MATCH関数が、VLOOKUP関数に劣る点があるとすれば、知名度が低いことである。同僚がこの関数を見た時、あなたが何をやっているのかわからないという事態は起こりうる。これは複数人で作業する上では問題になる可能性があるが、VLOOKUP関数が忘れ去られ、INDEX関数とMATCH関数の組み合わせが一般的になってしまえば、誰もこの関数の挙動を疑問に思うまい。
上で解説したINDEX関数とMATCH関数の組み合わせは、調べればいくらでも出てくる情報である。使い方を紹介しているWebページは、日本語のものだけでも無数にある。
それでも敢えて、改めて紹介するエントリを作成したのは、VLOOKUP関数ではなくINDEX関数+MATCH関数を使おうという意見を、強く訴えたかったがためである。それこそVLOOKUP撲滅キャンペーンの第一歩である。
この思想が広く世に受け入れられることを切に望む。
VLOOKUP関数を使うのはもうやめよう。
