スプレッドシートのVLOOKUPで部分一致(含む)で検索キーを指定する方法

GoogleスプレッドシートでVLOOKUPを行う際、検索キーに指定した値に対し、完全に一致したセルが抽出されます。

完全一致ではなく、検索キーの文言を含むといった部分一致でもVLOOKUPを実行する方法を解説します。

部分一致を応用することで、前方一致や後方一致などの検索条件も可能になります。

VLOOKUP関数とは

VLOOKUPは、Googleスプレッドシートに用意されている関数です。

以下のように4つの引数を指定する形で関数を利用します。

VLOOKUP(検索キー,検索範囲,指数,FALSE)

  1. 検索キー:検索条件となるキー(セルまたは数値・ワード)を設定します。
  2. 検索範囲:検索したいセルの範囲を設定します。
  3. 指数:検索キーがヒットしたところから○番目を表示します。
  4. FALSE:基本的にFALSEで、省略可能。

検索キーにヒットした列の情報を参照できるため、元々あるデータを見やすい形で整形し直すといった使い方で、スプレッドシートの関数でもよく利用されています。

VLOOKUPの検索条件は完全一致

VLOOKUP関数による検索は、基本的に完全一致するものが抽出されます。

検索範囲に指定したスプレッドシートのセル範囲の中から、値やセル指定などで引数にした検索キーと完全に同じ場合に条件合致したとして、指数の位置にあるセルが表示されます。

VLOOKUP関数で検索キーにマッチしなかった場合は、セルに「#N/A」が表示されます。

また、検索キーに一致したセルが複数個ある場合は、より上部の行がVLOOKUPの結果となります。

VLOOKUPで含むで抽出したいケースも

先程紹介した通り、通常のVLOOKUPの検索条件は完全一致のため、セル内の一部が合致するような部分一致は抽出できません。

ただ、検索キーを含んでいるという条件でVLOOKUPを表示したいというケースもあります。

完全一致だとオートフィルによる指定が難しいけれど、部分一致の場合オートフィルで抽出できる場合です。

部分一致によるVLOOKUPすることで、わかりやすく手間が少なくVLOOKUPが指定できます。

VLOOKUPの検索条件を部分一致(含む)にする方法

VLOOKUPの検索条件を通常の完全一致ではなく、セルに含んでいるという部分一致にすることも実は可能です。

VLOOKUPの1番目の引数である検索キーの記述方法を工夫することで、部分一致を表現することができます。

値で検索キーで「含む」を指定する方法

特定の文字列や数値などの値を検索キーとして、含むを指定するVLOOKUP関数の記述方法は以下になります。

=VLOOKUP(“*(検索キー)*”, 検索範囲,指数,FALSE)

通常の検索キーを*で囲うことで、検索キーの前後はどんな文字でもよくなり、部分一致で検索できるようになります。

GoogleスプレッドシートでVLOOKUPの部分一致を実演

例えば、上の図のようなスプレッドシートでA列のページに対し、VLOOKUPで部分一致でマッチする行を表示させてみます。

=VLOOKUP(“*1105*”,$A$2:$F$19,3,FALSE)

GoogleスプレッドシートでVLOOKUPで部分一致による検索を行う関数の記述方法

ページにある番号部分をVLOOKUPの検索キーとして、部分一致で抽出ができました。

セル参照による検索キーで「含む」を指定する方法

続いてセル参照によるVLOOKUPの検索キーで部分一致の含むの指定を行います。

記述方法は基本的に値の場合と同様ですが、セル参照の場合は、以下のような記述となります。

=VLOOKUP(“*”&(セル参照)&”*”, 検索範囲,指数,FALSE)

セル参照とアスタリスク”*”の間には、&(アンバサンド)で挟む形となります。

ダブルクオーテーション”で囲まれた範囲は文字列として認識されるため、セル参照と明示するために&を付与します。

先程の例で紹介したGoogleスプレッドシートで、セル参照による部分一致を行うと以下のようになります。

=VLOOKUP(“*”&H5&“*”,$A$2:$F$19,3,FALSE)

GoogleスプレッドシートでVLOOKUPで部分一致による検索を行う関数の記述方法(セル参照版)

セル参照で部分一致することで、記事IDでオートフィルしてVLOOKUPを利用するといった書き方が可能です。

VLOOKUPの前方一致や後方一致も表現可能

前述で紹介したVLOOKUPの部分一致で検索する方法を応用することで、含む以外にも「~から始まる(前方一致)」や「~で終わる(後方一致)」も可能になります。

部分一致の場合は、検索キーを2個のアスタリスク(*)で囲みました。

  • 1個目の前にあるアスタリスクを取り除く:「~から始まる」という前方一致の検索条件になります。
  • 2個目の後ろにあるアスタリスクを取り除く:「~で終わる」という後方一致の検索条件になります。

このようにVLOOKUP関数で「~を含む」の部分一致以外に、前方一致・後方一致も表現することも可能です。

まとめ・終わりに

今回、VLOOKUPの検索条件を完全一致ではなく、部分一致(含む)にする方法を紹介しました。

通常のVLOOKUPの検索では、検索キーと検索範囲のセルが完全一致したものが抽出されます。

しかし、VLOOKUPの検索キーの引数指定時に、アスタリスク(*)で検索ワードを挟むと、部分一致で抽出することが可能になります。

この方法を応用すると、前方一致や後方一致というパターンも表現可能です。

VLOOKUPを使う場合に、部分一致のほうがオートフィルを利用できて数式を書く手間が省けたり、見やすい場合があります。

VLOOKUPで検索キーを含んでいる行を抽出したい場合に、今回紹介した方法を試してみて下さい。