Office Hack

ExcelのVLOOKUP関数を使った様々な検索方法

  • by kenji
  • Release
  • Update

VLOOKUP関数には様々な検索方法があります。

まずは4番目の引数のFALSE/TRUEの「検索の型」の選択。そして基本的な機能である、「文字列の検索」。完全一致ではない「あいまいな検索」。そして2つの条件を検索値にする「複数検索」の方法をお伝えします。

最後に検索ができないときにどのような対処をしたら良いか例を交えてご説明します。

検索方法の種類

FALSE/TRUEの「検索の型」

VLOOKUP関数には4つの引数が必要です。書式としては「=VLOOKUP(検索値,範囲,列番号,[検索の型])」となっています。

検索方法の種類を決めるは4つ目の引数である「検索の型」になります。「検索の型」では、FALSEもしくはTRUEを指定します。

詳しくは以下のページの「第4引数(検索の型)FALSEとTRUEの違い」をご覧ください。

通常の「文字列検索」

VLOOKUP関数の用途として挙げられるのが、住所録の氏名から住所や電話番号を取り出すケースなどではないでしょうか。

VLOOKUP関数の検索値としては、数値の他にも文字列、セル参照を指定することができます。実際の例を挙げて説明していきます。

VLOOKUP関数の文字列検索

例の図のように市区町村と郵便番号のデータがあります。住所の数がたくさんある場合、この中から郵便番号を探すのは大変かと思います。住所を検索値としてVLOOKUP関数で郵便番号を抽出してみましょう。

VLOOKUP関数の入力

F4セルを選択し、『=VLOOKUP("東京都新宿区荒木町",B4:C8,2)』と入力します。検索したい住所を直接検索値として指定しています。文字列を指定するときは「"文字列"」のようにダブルクォーテーションで囲います。

VLOOKUP関数の結果

F4セルに検索結果である郵便番号の「160-0007」が表示されました。

文字列を検索値として指定する場合、全角文字と半角文字は区別して検索しますが、英字の大文字と小文字は区別しないので注意しましょう。例えば、「VLOOKUP」と「VLOOKUP」は区別して検索をしますが、「VLOOKUP」と「vlookup」は区別せずに検索します。

VLOOKUP関数のセル参照

次に検索値にセル参照を用いてみます。F4セルを選択し、『=VLOOKUP(E4,B4:C8,2)』と入力します。E4セルに入力した文字列を参照して検索します。

VLOOKUP関数のセル参照結果

E4セルに『東京都新宿区荒木町』と入力すると、F4セルに郵便番号の「160-0007」が表示されました。

ワイルドカードを使った「あいまい検索」

ワイルドカードという記号を使って、あいまいな検索をすることが可能です。

  • 「*」を使って、文字数を決めないで任意の文字を検索できます。配置する場所は自由です。
  • 「?」を使って、任意の 1 文字を検索できます。配置する場所は自由です。

また、あいまいという意味では、上部で説明しました「検索の型」のTRUEを指定すると近似値で検索します。

以下の記事でワイルドカードの利用方法を説明します。

2つの条件を検索値にする「複数検索」

VLOOKUP関数では条件を1つしか指定できないが、複数の条件を指定したいといったニーズはたくさんあります。

VLOOKUPの複数条件

例えば上記の表で、商品名の「りんご」の価格だけ取り出すなら簡単ですが、種類の「果物」と商品名の「りんご」を2つの条件として価格を取り出すのは、工夫が必要です。

以下の記事で複数条件で検索する方法を説明します。

検索できないときの対処法

VLOOKUP関数を使用していると「#N/A!」エラーが出たり、意図しない結果になったりすることもあります。検索できない主な例を挙げながらその対処方法を説明していきます。エラーになった際に確認してみましょう。

検索値が検索範囲の左端の列にない

検索範囲の誤った例

例えば上記の表で、C10セルに商品コードを入れるとD10セルの商品名に価格テーブルから紐づいた商品名を表示するようにしたいとします。もし商品名を出したいから検索範囲も商品名からで大丈夫と思い、商品コードを含まないC4からD6セルの範囲を指定した場合、「#N/A!」エラーが表示されます。

VLOOKUP関数の第2引数である「検索範囲」は第1引数の「検索値」を左端の列に含むようにしなければなりません。上記の例では、正しくは『=VLOOKUP(C10,$B$4:$D$6,2,FALSE)』のように検索値である「商品コード」を左端の列にしたB4からD6セルの範囲を指定する必要があります。

完全一致を指定していて見つからない

完全一致にして見つからなかった例

VLOOKUP関数の第4引数の「検索の型」を間違って使用したパターンをご紹介します。例えば上記の表で、C10セルに商品コードを入れるとD10セルの商品名に価格テーブルから紐づいた商品名を表示するようにしたいとします。D10セルには『=VLOOKUP(C10,$B$4:$D$6,2,FALSE)』と入力しました。次にC10セルに商品コード『1000』を入力すると「#N/A!」エラーになりました。

例では、「検索の型」として「FALSE」を指定しています。これは完全一致でなければエラーを返します。完全一致したいにも関わらず、価格テーブルの商品コードにない商品コードを入力したのでエラーになりました。もし近似値でもよければ「検索の型」に「TRUE」を指定しましょう。その際は列番号に指定した列は昇順にしておく必要があります。

検索範囲を「絶対参照」にしていないためズレてしまう

絶対参照にしていない例

例えば上記の表で、C10セルに商品コードを入れるとD10セルの商品名に価格テーブルから紐づいた商品名を表示するようにしたいとします。D10セルに『=VLOOKUP(C10,B4:D6,2,FALSE)』と入力しました。C10セルに商品コード『1001』と入力するとD10セルに正しく「りんご」と表示されます。今度はC10セルの式をC11セルにコピーします。

検索範囲がずれてしまった例

検索範囲を確認すると、B5からD7セルの範囲が変わってしまっています。これはセル範囲を絶対参照にしていなかったために発生してしまった例です。元となるデータ範囲は絶対参照にしましょう。

その他の検索できない例と対処方法を下記の記事で解説しています。

VLOOKUP関数専門の本をご紹介

VLOOKUP関数をより深く勉強したい方はこちらの本でさらに理解を深めてみてはいかがでしょうか?実際の操作画面を示しながら、徹底的にわかりやすく解説したフルカラー入門書です。

VLOOKUP関数のツボとコツがゼッタイにわかる本

立山秀利(著)
206ページ

その他、様々なVLOOKUP関数の使い方

XLOOKUP関数も合わせてチェック!

VLOOKUP関数の後継として新しくXLOOKUP関数が発表されました。XLOOKUP関数の基本的な使い方を説明しています。

他の関数も合わせてチェック!

Office Hackでは、ここでご紹介できなかった関数の一覧ページもご用意しております。ぜひ、参考にしてください。