ExcelのVLOOKUP関数でワイルドカードを使って部分一致検索する方法

Excel(エクセル)のVLOOKUP関数で検索をかけるときに

  • 部分一致で検索したい
  • 前方一致、後方一致で検索したい
  • あいまい検索したい

というシチュエーションがありますよね?

そんなときは「ワイルドカード」を使えば簡単です。

今日はVLOOKUP関数 × ワイルドカード部分一致検索する方法を紹介していきますよ。

VLOOKUP関数でワイルドカードを使って部分一致検索する方法

そもそもワイルドカードとは?

はじめてワイルドカードを使う方や、使い方の基本について知りたいという方はこちらの記事からどうぞ。

ワイルドカードは文字列に置き換えて一致する条件を指定することができる特殊記号で、これを使えば「不特定の文字列を指定して検索する」ことができるんですよ。

そしてExcelで主に使うワイルドカードとしては2種類「*」「?」があります。

Excelで主に使うワイルドカードはこれ

  1. 「*」半角アスタリスク
    不特定な文字数の文字列を示す
    (何文字でもいいからとにかく文字列みたいな感じ)
  2. 「?」半角クエスチョンマーク
    文字数1文字を示す
    「???」なら3文字を表すように?の重ねる個数によって文字数を指定することができる

付与の仕方に注意

  • 文字列につけるとき
    直接くっつけて両端を「"」で囲む
    例)"ねこ*" "?ねこ"
  • セル番号につけるとき
    「&」でつないだうえでワイルドカードだけを「”」で囲む
    例)E4&"*" "?"&E4

ワイルドカードの使用時の注意(セル番号には&を使ってつなげる)

では、本題のVLOOKUP関数での使い方を解説していきます。

「*」を使って文字数が不特定の文字列を指定する

まずは「*(半角アスタリスク)」の使い方を解説していきますね。

「*」は、文字数を問わずに0文字以上の文字列を示します。

冒頭でも話したように、部分一致にも「前方一致」「後方一致」「中間一致」などパターンがあるのでそのパターン別に見てみましょう!

前方一致のパターン

前方一致はこういう一致の仕方のこと

「ねこ○○」「りんご○○」

セルD5に入力した文字列に前方一致するスニーカー(A列)を検索して一致データに該当する価格(B列)を取り出してくる場合を例としましょう。
2_VLOOKUP関数で「*」を使って前方一致をする場合のデータ例

ここでは、セルD5に入力した文字列の前方一致で検索したいので

セルE5に入力する関数式は
=VLOOKUP(D5&"*",$A$3:$B$8,2,0)

セル番号で指定する場合は「&」と「"」を使ってつなげなくてはならないので、検索値は「D5&"*"」と指定する
VLOOKUP関数で「*」を使って前方一致をする場合のワイルドカードの使い方

D5には「adidas」と入力しているので実質的には「adidas*」で検索するのと同じことになります。

 ポイント文字列を直接入力してもOK

このとき関数式内の検索値に「adidas*」と直接入力して「=VLOOKUP("adidas*",$A$3:$B$8,2,0)」という式で計算するのもできます、どちらの方法でもOK!

そうすると、A列の「adidasスニーカー / A-1」に一致して価格「¥12,800」が取り出されてきました。ちゃんと前方一致でデータを拾えてきましたね!
VLOOKUP関数で「*」を使った前方一致検索によってデータを取り出した

これは前方一致なので、もしA列にある文字列が「adidasスニーカー /A-1」とかだった場合には一致しません。

ですが、「*」は文字数は問わない文字列を指定するので置き換える文字が0文字である「adidas」にも一致します。

後方一致のパターン

後方一致はこういう一致の仕方のこと

「○○ねこ」「○○りんご」

前方一致と同じデータを使って、

今度は、セルD8に入力した文字列に後方一致するスニーカー(A列)を検索して一致データに該当する価格(B列)を取り出してくる場合を例としましょう。
2_VLOOKUP関数で「*」を使って後方一致をする場合のデータ例

今度は、セルD8に入力した文字列の後方一致で検索したいので

セルD8に入力する関数式は
=VLOOKUP("*"&D8,$A$3:$B$8,2,0)

セル番号で指定する場合は「&」と「"」を使ってつなげなくてはならないので、検索値は「"*"&D8」と指定する
VLOOKUP関数で「*」を使って後方一致をする場合のワイルドカードの使い方

今度はD8には「A-1」と入力しているで実質的には「*A-1」で検索するのと同じことになります。

 ポイント文字列を直接入力してもOK

このとき関数式内の検索値に「*A-1」と直接入力して「=VLOOKUP("*A-1",$A$3:$B$8,2,0)」という式で計算するのもできます、どちらの方法でもOK!

そうすると、A列の「adidasスニーカー / A-1」に一致して価格「¥12,800」と表示されました。ちゃんと後方一致でデータを取り出してくることができましたね!
VLOOKUP関数で「*」を使った後方一致検索によってデータを取り出した

今度は後方一致なので、もしA列にある文字列が「adidasスニーカー / A-12」とかだった場合には一致しません。

ですが、「*」は文字数は問わないので置き換える文字が0文字である「A-1」にも一致します。

中間一致のパターン

中間一致はこういう一致の仕方のこと

「○○ねこ○○」「○○りんご〇」

さきほど使用したデータのA列の内容に少し手を加えました。

セルD5に入力した文字列に中間一致するスニーカー(A列)を検索して一致データに該当する価格(B列)を取り出してくる場合を例としましょう。
2_VLOOKUP関数で「*」を使って中間一致をする場合のデータ例

セルD5に入力した文字列の今度は中間一致で検索したいので・・・

セルD5に入力する関数式は
=VLOOKUP("*"&D5&"*",$A$3:$B$8,2,0)

セル番号で指定する場合は「&」と「"」を使ってつなげなくてはならないので、検索値は「"*"&D5&"*"」となる
VLOOKUP関数で「*」を使って中間一致させる場合のワイルドカードの使い方

D5には「pumaスニーカー」と入力しているので実質的には「*pumaスニーカー*」で検索するのと同じことになります。

 ポイント文字列を直接入力してもOK

このとき関数式内の検索値に「*pumaスニーカー*」と直接入力して「=VLOOKUP("*pumaスニーカー*",$A$3:$B$8,2,0)」という式で計算するのもできます、どちらの方法でもOK!

そうすると、A列の「D_pumaスニーカー / D-4」に一致して今度は価格が「¥6,700」と表示されました。ちゃんと中間一致でデータを取り出してくることができていますね!
VLOOKUP関数で「*」を使った中間一致検索によってデータを取り出した

今回は中間一致なので、もしA列にある文字列が「pumaスニーカー / D-4」とかだった場合には一致し・・・ます!(笑)

前述でも説明したように、「*」は文字数は問わないずに0文字以上の文字列するので「pumaスニーカー」といったように前後に文字がない場合にも一致するんですね。

「?」を使って文字数1文字の文字列を指定する

今度は「?(半角クエスチョンマーク)」の使い方を解説していきますよ。

「?」は、不特定の1文字の文字を示します。
「??」とすれば2文字を、「?????」とすれば5文字をというように?の重ねる個数によって指定文字数を決めることができます。

「*」と同じように、文字列やセル番号の前後にくっつけることで文字列の置き換えとして指定をすることができます。

置き換えたい文字数分の「?」を重ねて指定する

セルD5に入力した文字列に前後一致するスニーカー(A列)を検索して一致データに該当する価格(B列)を取り出してくる場合を例としましょう。

今回は、A列データのブランド名部分の文字数を一致するしないの判別キーとして考えることにします。(adidas=6文字、nike=4文字、new balance=11文字、puma=4文字、VANS=4文字)VLOOKUP関数で「?」を使って部分一致をする場合のデータ例

例えばセルE5に「スニーカー」と入力したときに「adidasスニーカー / A-1」と一致させてその価格を取り出したいとします

「adidas」は6文字
「  / A-1」はスペースを入れて7文字

なので、
セルE5に入力する関数式は
=VLOOKUP("??????"&D5&"???????",$A$3:$B$8,2,0)

セル番号で指定する場合は「&」と「"」を使ってつなげなくてはならないので、検索値は「"??????"&D5&"???????"」とする
VLOOKUP関数で「*」を使って前方一致をする場合のワイルドカードの使い方

D5には「スニーカー」と入力しているので実質的には「6文字の文字列 + スニーカー + 7文字の文字列」の内容を探して検索していることになりますね。

 ポイント後半部分の「?」は「*」を使って指定してもOK

「  / A-1」部分の置き換えとして「???????(?7つ)」と指定していましたが、A列データの末尾部分はどれも同じ文字数であって末尾部分による判別はできない(一致は問わない)ため0文字以上の文字列という考え方で「*」を使って「=VLOOKUP("??????"&D5&"*",$A$3:$B$8,2,0)」としてもOKです

そうすると、A列の「adidasスニーカー / A-1」に一致して価格「¥12,800」が取り出されてきましたね。しっかり?で一致させてデータを拾えましたね。
VLOOKUP関数で「*」を使った前方一致検索によってデータを取り出した

1つ注意点があります。

 注意

今回はA列データのブランド名の部分の文字数が6文字であるデータが「adidas」しかないためVLOOKUP関数によって取り出すことができた例です。もし他に「UNIQLOスニーカー /  E-1」など「??????スニーカー*」に一致するデータがA列に存在する場合は成り立たないので注意しましょう。

※VLOOKUPは一致するデータが2つ以上ある場合は、位置的に最も上に入力されているデータを取り出してくるので一応データは表示されますが

上の注意点言ったように、他にもA列に「6文字 + スニーカー + 7文字」となるデータがある場合はこの指定方法は使えません。

他に「?」の便利な使い方としては、「き?こ(きのこ、きんこなどに一致)」や「き?く(きおく、きろくなどに一致)」と文字列の間に挟んで文字を置き換えることもできるので便利ですよ。

ワイルドカードをちゃんと使ってるのにエラー(#N/A)になっちゃうのはどうして?

ワイルドカードを使った関数計算で、エラーになってしまう原因でよくあるものを紹介します。上手くいかないときはどこかおかしいはずなので確認してみてくださいね!

ワイルドカードは文字列には一致するがその他の数値・数式などには一致しない

使用しているデータが「数値」や「数式」ではありませんか?

ワイルドカードは、文字列には一致しますが数値や数式などその他のデータには一致することができないため戻り値は「#N/A」というようにエラー表示で返ってきます。
ワイルドカードが上手く反映されない理由

ワイルドカードの付与の仕方は文字列につけるかセル番号につけるかで異なる

ワイルドカードの付与の仕方にミスはありませんか?

ワイルドカードは(関数式内などで使う場合)、

「*」に付与するときは直接くっつけて「"」で囲いますが、「?」に付与するときは「&」でつないだうえでワイルドカードのみ「"」で囲う必要があります。

*(半角アスタリスク)?(半角クエスチョンマーク)
ねこ"ねこ*""ねこ?"
E4E4&"*"E4&"?"

Excel初心者さんにおすすめ

この記事を見ている人におすすめ


今日は、VLOOKUP関数でワイルドカードを使って部分一致検索する方法を紹介してきました。

ワイルドカードは、SUMIFやCOUNIFを始めとするさまざまな関数でも使えます。関連記事からチェックしてみてくださいね。