ルックアップ関数の特徴と相違点（VLOOKUP、HLOOKUP編）

ルックアップ系の関数の中で代表的な存在になっているのが「関数VLOOKUP」だ。ただし、この関数にも弱点がある。「*」（ワイルドカード）を使って「部分一致」で検索する方法を含めて、基本的な動作を確認しておこう。また、関連する関数として「関数HLOOKUP」の使い方も紹介しておく。こちらは参考程度に覚えておけば十分だ。

使い勝手を改善した関数VLOOKUP

今回は「関数VLOOKUP」の使い方を紹介していこう。前回の連載で紹介した「関数LOOKUP」と似たような機能を持つ関数であるが、その書式や使い方は大きく異なる。また、参考として「関数HLOOKUP」の使い方も簡単に紹介しておこう。

関数VLOOKUPと関数HLOOKUPの使い方、注意点

今回も、以下の図に示した会員名簿を使って具体的な使用例を紹介していく。この名簿には、ワークシートの11行目から510行目までに計500件のデータが入力されている。

会員情報をまとめた表

ここでは「関数VLOOKUP」の使用例として、「氏名カナ」を入力すると、該当する会員の郵便番号、住所を自動取得する機能を作成してみよう。

◆関数VLOOKUPの書式
　=VLOOKUP(検索値, データ範囲, 列番号, TRUE/FALSE)

前回の連載で紹介した関数LOOKUPと同様に、第1引数には検索する「数値」または「文字」をセル参照などで指定する。

続いて、第2引数に「データの範囲」を指定し、その中の「何列目のデータを取得するか？」を第3引数に指定する。

最後の第4引数には「TRUE」または「FALSE」を指定する。TRUEを指定した場合は「近似一致」、FALSEを指定した場合は「完全一致」でデータが検索される。通常は、FALSE（完全一致）を指定しておくのが基本だ。

具体的な例を示していこう。今回の例では、検索値となる「氏名カナ」をC3セルに入力する。よって、第1引数には「C3」を指定すればよい。第2引数には「氏名カナ」の列を先頭にしたデータ範囲「C11:H510」を指定する。続いて、その中の「何列目のデータを取得するか？」を第3引数に指定する。たとえば「郵便番号」の場合、データ範囲の3列目に相当しているので「3」を指定する。最後に「FALSE」（完全一致）を指定すると、関数VLOOKUPの入力が完了する。

「郵便番号」を取得する関数VLOOKUPの入力

「Enter」キーを押して関数VLOOKUPを実行すると、「イワイケイコ」さんの郵便番号が正しく取得されているのを確認できる。

関数VLOOKUPにより取得された「郵便番号」

他のデータを取得するときも基本的な考え方は同じだ。たとえば「住所1」はデータ範囲の4列目にあるので、第3引数に「4」を指定すると「住所1」のデータを取得できる。

「住所1」を取得する関数VLOOKUPの入力

同様に、第3引数に「5」を指定すると「住所2」のデータを取得できる。これで「氏名カナ」に対応する郵便番号、住所を自動取得することが可能となる。

関数VLOOKUPにより取得されたデータ

このように、表を並べ替えなくても「正しいデータ」を取得できるのが関数VLOOKUPの利点となる。

ワイルドカードを活用した「部分一致」の検索

続いては、「氏名カナ」の一部だけを入力して、「前方一致」や「後方一致」で検索する方法を紹介していこう。状況がわかりやすくなるように、「氏名カナ」のデータを取得する関数VLOOKUPを追加した例で解説していく。

「氏名カナ」を取得する関数VLOOKUPの入力

まずは、「氏名カナ」の一部をそのまま入力した例だ。たとえば、「イワイ」のキーワードだけで検索すると、関数VLOOKUPの結果は「#N/A」のエラーになる。このエラーは、該当するデータが見つからなかったこと示している。

最初の数文字だけを入力した場合

第4引数に「FALSE」を指定すると「完全一致」で検索が行われるため、1文字でもキーワードが異なると「#N/A」のエラーになる。今回の例では、「氏名カナ」の列に「イワイケイコ」は存在するものの、「イワイ」だけのデータは存在していない。よって、関数VLOOKUPの結果はエラーになる。

前回の連載で紹介した「関数LOOKUP」は、検索値が見つからなかった場合に（勝手に）近似一致でデータを取得する仕様になっていた。このため、間違ったデータが取得されてしまう危険性があった。

一方、「関数VLOOKUP」は完全一致で検索が行われるため、間違ったデータが取得される可能性は極めて小さくなる（※）。見方によっては、これも関数VLOOUPの利点の一つと考えられるだろう。

（※）第4引数に「FALSE」を指定した場合。

どうしても部分一致で検索したい場合は、半角の「*」（ワイルドカード）を追加して検索を実行すればよい。

たとえば、「イワイ*」と入力すると、「氏名カナ」が「イワイ」で始まるデータを取得できる。

ワイルドカードを使って「前方一致」で検索した場合

最初に「*」を記述して、後方一致でデータを検索することも可能だ。

たとえば、「*ノゾミ」と入力すると、「氏名カナ」が「ノゾミ」で終わるデータを取得できる。

ワイルドカードを使って「後方一致」で検索した場合

ただし、上図を見るとわかるように、該当するデータが複数ある場合は、その中で一番上にあるデータだけが取得される仕組みになっている。このため、必ずしも意図していたデータを取得できるとは限らない。

該当するデータが複数ある場合は、それを一覧形式でリスト表示してくれると便利なのだが、そのような機能は用意されていない。これはルックアップ系のすべての関数に共通する弱点といえる。このため、氏名などで検索するときは「同姓同名の会員が存在していないか？」に注意しておく必要がある。

ルックアップ系の関数を使用するときは、ID番号のように「絶対に重複しないデータ」で検索するのが確実な使い方といえる。とはいえ、状況によっては「ID番号では検索しづらい・・・」というケースもあるだろう。

不安な方は、「検索値に一致するデータが複数ないか？」をチェックする機能を設けておくとよいだろう。単に重複データの有無（個数）をチェックするだけなら「関数COUNTIF」で手軽に実現できる。

なお、「2番目以降のデータも取得したい」という場合は、関数FILTERを利用するなど、根本的に異なる手法を採用しなければならない。これについては、いずれ詳しく紹介していくとしよう。

関数VLOOKUPの弱点

続いては、関数VLOOKUPの弱点について解説していこう。関数VLOOKUPの最大の弱点は、「検索する列」より左側にあるデータを取得できないことだ。

たとえば「氏名カナ」で検索して、漢字表記の「氏名」を取得したい場合はどうすればよいだろうか？　以下の図のように、B列を含めてデータ範囲を指定すると、関数VLOOKUPの結果はエラーになってしまう。

「氏名」を取得しようと試みた場合

関数VLOOKUPの結果（エラー）

これは関数VLOOKUPの仕様に従った結果といえる。関数VLOOKUPは、第2引数で指定したデータ範囲の「左端の列」だけを検索対象にする仕様になっている。上記のように第2引数を指定した場合、B列の11～510行目が検索対象になる。B列には漢字表記の「氏名」が入力されており、カナ表記の「イワイケイコ」は存在しない。よって、値が見つからないことを示す「#N/A」のエラーになってしまう。

要するに、関数VLOOKUPでデータを自動取得できるのは、「検索する列より右側にあるデータ」だけに限定されることになる。これが関数VLOOKUPの最大の弱点といえる。