Tips: 検索の型（第4引数）がTRUEの時の VLOOKUP

VLOOKUP()関数の第4引数、いままでは何も考えずに FALSE　を入力してきたが、FALSEがあるなら TRUEもあるはず。実は、ある。このへんはヘルプでVLOOKUP()を調べると一応書いてあるのだけれど、とても日本語として分かりづらい。以下、引用

検索の型 省略可能です。VLOOKUP を使用して検索値と完全に一致する値だけを検索するか、その近似値を含めて検索するかを指定する論理値です。
TRUE を指定するか省略すると、検索値と完全に一致する値、またはその近似値が返されます。完全に一致する値が見つからない場合は、検索値未満の最大値が使用されます。

余りにも何が書いてあるか分からないので、もうちょっと整理してみる。TRUEを指定すると、以下のことが起きる。

• 検索値（第1引数）と完全に一致する値が検索対象範囲（第2引数）の左端列にあれば、一致した行の中から第3引数で指定された列にあるセルの値を返す→FALSEと同じ動作
• 検索値（第1引数）と完全に一致する値が検索対象範囲（第2引数）の左端列になければ、検索対象範囲の左端列を検索値と比較し、検索値を超えた行の１つ上の行を「一致した行」として扱う。

こうやって書き直してもやっぱりよく分からない。こういうときは実例を作ってやってみよう。

E2セルには、A2:B10を検索対象範囲、D2を検索値にしているVLOOKUP関数が入っている。

ヘルプには「検索値未満の最大値」と書いてあるけれど……

上述の通り、ヘルプには検索対象範囲の左端列の中から「検索値未満の最大値」を探し出すみたいなことが書いてあるが、実際にそんな面倒なことをしているわけがない。実際の動作は左端列の中で検索値を探して、超えたところが見つかったらその一行上。

上の例では、検索対象範囲の左端列が昇順に並べ替えられていたので、「検索値を探して、検索値を超えた行の一行上」でも「検索値未満の最大値」は一致する。しかし、左端列が昇順に並べ替えられていない場合は変なことになる。変な例は下に示すとおり。

※以下の内容はExcelの挙動から推定した内容なので、本当かどうかは分かりません。注意してください。

上のケースでは、結果は以下のようになる。上の行から順番に探索しているのであれば、4はそのまま「完全に一致する値」があるのだから、「い」を返すはず。でも、なぜか「え」が返っている。

「データは並べ替え済み」という仮定があるときに、探索を高速に行う方法として、バイナリサーチと呼ばれる方法がある。Excelの内部でもこれに類した方法を使っていると考えると、2〜7は「え」で、8から正しい値になるのかが大体説明できる。

というわけで、ヘルプにも書いてある通り「左端列を昇順に並べ替えておく」が、検索の型（第4引数）を TRUEとした VLOOKUPではとっても重要、というか、この並べ替えをやっていなかったら、結果は何を表しているのか全く分からないことになる。気をつけよう。