VLOOKUP関数で特定の文字列を含む部分一致で検索する方法

Excel(エクセル)「VLOOKUP関数を使って特定の文字列を含む部分一致で検索する方法」を解説していきます。

なお本記事では、ワイルドカードの記述の仕方に関する解説は省いているので以下の記事でチェックくださいね。

Excel(エクセル)のVLOOKUP関数で特定の文字列を含む部分一致で検索する方法

部分一致にもパターンがあります。例えば文字の前方部分を一致させたい…など、一致させる部分によって記述の仕方も変わってきます。

今回は、その一致パターンの中でも使用頻度の多い①前方一致②後方一致③中間一致でのやり方を解説していきますね。

本題に入るまえの予備知識

VLOOKUP関数の公式

=VLOOKUP( 検索値 , 検索範囲 , 列番号 , 検索方法 )

列番号:取り出したいデータが範囲中の左から数えて何列目か
検索方法:検索の形式を完全一致とするか近似値検索にするか

ワイルドカードの予備知識

ワイルドカードは
文字に付与する場合は「”文字列*”」のように直接くっつけることができますが、セル番号に付与する場合は「&」を使って「&"*"」の形でくっつけなければなりません

ワイルドカードの付け方のイメージ

例①前方一致で検索する

まずは前方一致で検索する例です。

前方一致っていうのは、「ねこ○○ ねこ×× ねこ△」のように先頭の「ねこ」という文字の一致をみて検索するパターンのことです。

VLOOKUP関数 × 文字列の前方一致

例:留学生一覧表から名前のファーストネームのみの部分一致で検索し該当者の性別を取り出したい
前方一致で検索する例

セルF4にVLOOKUP関数を入力していく(検索値はセルE4の前方一致を指定)

検索値:「E4&"・*"」
セルE4に入力した文字列の前方一致で指定したいのでワイルドカードを使って「E4&"・*"」とする(レイス・には一致させたくないので「レイ・」までを一致対象とする)


検索範囲:$A$3:$C$10
範囲固定したいので絶対参照にする


列番号:2



検索の型:0
完全一致で検索したいので「0」もしくは「FALSE」と記述

 ①の引数を公式に当てはめると以下のような式になる
=VLOOKUP(E4&"・*",$A$3:$C$10,2,0)
前方一致で検索する(アスタリスクと&を使って検索値を設定する)

セルE4で指定したデータに一致する人物の「性別」が算出される
前方一致で検索する(検索値の前方に一致するデータが表示された)



さらに・・・出身地も引っぱり出したい場合にはF4の関数をG4にコピーして列番号だけ変更すればOK

列番号:3
今度は検索範囲のうち取り出したいデータは左から「3」列目

=VLOOKUP(E4&"・*",$A$3:$C$10,3,0)
前方一致で検索する(関数をコピーして列番号のみを変更する)

前方一致で検索する(列番号を変更した関数式を入力する)

セルE4で指定した人物の「出身」も表示される
前方一致で検索する(検索値に前方一致したデータが表示された)

また、今後セルE4の内容を変更しない場合や「レイ・ブラウン」だけのデータがとれればよい時は、関数内の検索値を直接文字列で「=VLOOKUP("レイ・*",$A$3:$C$10,2,0)」と指定する方法もあります。

ちなみに「*」のワイルドカードは0文字以上の文字を代替する記号なので「レイ・」とだけ入力されたデータが存在する場合はそれにも一致してしまうので注意。

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

例②後方一致で検索する

次は後方一致で検索する例です。

後方一致っていうのは、「○○ねこ ××ねこ △ねこ」
のように末尾の「ねこ」という文字の一致をみて検索するパターンのことです。


VLOOKUP関数 × 文字列の前方一致

例:東京の地域一覧表から市区町村のみの部分一致で検索して、「よみ」を取り出したい
後方一致で検索する例


セルF4にVLOOKUP関数を入力していく(検索値はセルE4の後方一致を指定)

検索値:「"*"&E4」
セルE4に入力した文字列を後方一致させたいので「"*"&E4」と記述

検索範囲:$A$3:$C$13
範囲をずらしたくないので絶対参照にして「$A$3:$C$13」とする

列番号: 2
検索範囲のうち取り出したいデータは 左から数えて「2」列目

検索の型:0
完全一致での検索方法を選ぶので「0」もしくは「FALSE」と記述


①の引数を公式にあてはめると以下のような式になる=VLOOKUP(E4&”・*”,$A$3:$C$13,2,0)
検索値にE4の後方一致の指定

指定した市区町村の「よみ(文字列)」が表示される
検索値に一致したデータが表示




ここからさらに、「人口」も取り出したい場合はセルE4の関数をコピーして列番号だけ変更すればOK

列番号:3
検索範囲のうち取り出したいデータは 左から数えて3列目

=VLOOKUP(E4&”・*”,$A$3:$C$13,3,0)

後方一致で検索する(関数をコピーして列番号のみ変更

これで指定した市区町村の「人口(文字列)」も表示される
検索値に一致したデータが表示

文字列で指定する方法も また、以降E4のデータを変更しないor東京都港区だけのデータがとれればよい場合は、検索条件を文字列で指定して「=VLOOKUP(“*港区”,$A$3:$C$13,2,0)」でもOK

例③中間一致で検索する

さいごに、中間一致で検索する例です。

中間一致っていうのは、「○ねこ○ ×ねこ× △ねこ△」
のように中間の「ねこ」という文字の一致をみて検索するパターンのことです。

VLOOKUP関数 × 中間一致

例:漫画キャラ一覧表の中から1人のキャラ名(の一部分)の部分一致で検索して漫画タイトルを取り出したい
中間一致で検索する例

セルF4にVLOOKUP関数を入力していく(セルE4の中間一致で指定)

検索値:"*"&E4&"*"
ルE4に入力した文字列の中間一致で検索したいので「"*"&E4&"*"」と記述


検索範囲:$A$3:$C$8
検索をかけたい範囲はA3:C8ですが、関数を横へコピーしたときに範囲をずらしたくないので絶対参照にして「$A$3:$C$8」とする


列番号:2
検索範囲のうち取り出したいデータは 左から数えて「2」列目


検索の型:0
完全一致での検索方法を選ぶので「0」もしくは「FALSE」と記述


 ①の引数を公式にあてはめると以下のような式になる=VLOOKUP("*"&E4&"*",$A$3:$C$8,2,0)
検索値を中間一致で指定

指定したデータに該当する「漫画タイトル」が表示される
検索値に中間一致したデータが表示される



ここからさらに、「連載雑誌」も取り出したい場合はE4の関数をコピーして列番号だけ変更すればOK

列番号:3
検索範囲のうち取り出したいデータは 左から3列目

=VLOOKUP("*"&E4&"*",$A$3:$C$8,3,0)
関数をコピーして列番号のみを変更

列番号を変更した関数式を入力

指定したデータに該当する「連載雑誌」も表示される
検索値に中間一致したデータが表示された

文字列で指定する方法もまた、以降E4のデータを変更しない場合や「リヴァイ・アッカーマン」だけのデータをだせればOKな場合には「=VLOOKUP(“*リヴァイ*”,$A$3:$C$8,2,0)」のように検索値を直接文字列で指定する方法もOK

話題のExcelベストセラー本(Amazonレビュー:

話題のExcelベストセラー本(Amazonレビュー:

以上、今日はVLOOKUP関数で特定の文字列を含む部分一致で検索する方法を解説してきました。

今回は検索値をセル番号で指定する例をメインに解説してきましたが、文字列でも指定ができるので、シチュエーションに応じて臨機応変に対応するようにしましょう。