スプレッドシートのVLOOKUPで部分一致(含む)で検索キーを指定する方法
GoogleスプレッドシートでVLOOKUPを行う際、検索キーに指定した値に対し、完全に一致したセルが抽出されます。
完全一致ではなく、検索キーの文言を含むといった部分一致でもVLOOKUPを実行する方法を解説します。
部分一致を応用することで、前方一致や後方一致などの検索条件も可能になります。
VLOOKUP関数とは
VLOOKUPは、Googleスプレッドシートに用意されている関数です。
以下のように4つの引数を指定する形で関数を利用します。
VLOOKUP(検索キー,検索範囲,指数,FALSE)
- 検索キー：検索条件となるキー(セルまたは数値・ワード)を設定します。
- 検索範囲：検索したいセルの範囲を設定します。
- 指数：検索キーがヒットしたところから○番目を表示します。
- FALSE：基本的にFALSEで、省略可能。
検索キーにヒットした列の情報を参照できるため、元々あるデータを見やすい形で整形し直すといった使い方で、スプレッドシートの関数でもよく利用されています。
VLOOKUPの検索条件は完全一致
VLOOKUP関数による検索は、基本的に完全一致するものが抽出されます。
検索範囲に指定したスプレッドシートのセル範囲の中から、値やセル指定などで引数にした検索キーと完全に同じ場合に条件合致したとして、指数の位置にあるセルが表示されます。
VLOOKUP関数で検索キーにマッチしなかった場合は、セルに「#N/A」が表示されます。
また、検索キーに一致したセルが複数個ある場合は、より上部の行がVLOOKUPの結果となります。
VLOOKUPで含むで抽出したいケースも
先程紹介した通り、通常のVLOOKUPの検索条件は完全一致のため、セル内の一部が合致するような部分一致は抽出できません。
ただ、検索キーを含んでいるという条件でVLOOKUPを表示したいというケースもあります。
完全一致だとオートフィルによる指定が難しいけれど、部分一致の場合オートフィルで抽出できる場合です。
部分一致によるVLOOKUPすることで、わかりやすく手間が少なくVLOOKUPが指定できます。
VLOOKUPの検索条件を部分一致(含む)にする方法
VLOOKUPの検索条件を通常の完全一致ではなく、セルに含んでいるという部分一致にすることも実は可能です。
VLOOKUPの1番目の引数である検索キーの記述方法を工夫することで、部分一致を表現することができます。
値で検索キーで「含む」を指定する方法
特定の文字列や数値などの値を検索キーとして、含むを指定するVLOOKUP関数の記述方法は以下になります。
=VLOOKUP(“*(検索キー)*", 検索範囲,指数,FALSE)
通常の検索キーを*で囲うことで、検索キーの前後はどんな文字でもよくなり、部分一致で検索できるようになります。
例えば、上の図のようなスプレッドシートでA列のページに対し、VLOOKUPで部分一致でマッチする行を表示させてみます。
=VLOOKUP(“*1105*",$A$2:$F$19,3,FALSE)
ページにある番号部分をVLOOKUPの検索キーとして、部分一致で抽出ができました。
セル参照による検索キーで「含む」を指定する方法
続いてセル参照によるVLOOKUPの検索キーで部分一致の含むの指定を行います。
記述方法は基本的に値の場合と同様ですが、セル参照の場合は、以下のような記述となります。
=VLOOKUP(“*"&(セル参照)&"*", 検索範囲,指数,FALSE)
セル参照とアスタリスク"*"の間には、&(アンバサンド)で挟む形となります。
ダブルクオーテーション"で囲まれた範囲は文字列として認識されるため、セル参照と明示するために&を付与します。
先程の例で紹介したGoogleスプレッドシートで、セル参照による部分一致を行うと以下のようになります。
=VLOOKUP(“*"&H5&“*",$A$2:$F$19,3,FALSE)
セル参照で部分一致することで、記事IDでオートフィルしてVLOOKUPを利用するといった書き方が可能です。
VLOOKUPの前方一致や後方一致も表現可能
前述で紹介したVLOOKUPの部分一致で検索する方法を応用することで、含む以外にも「～から始まる(前方一致)」や「～で終わる(後方一致)」も可能になります。
部分一致の場合は、検索キーを2個のアスタリスク(*)で囲みました。
- 1個目の前にあるアスタリスクを取り除く：「～から始まる」という前方一致の検索条件になります。
- 2個目の後ろにあるアスタリスクを取り除く：「～で終わる」という後方一致の検索条件になります。
このようにVLOOKUP関数で「～を含む」の部分一致以外に、前方一致・後方一致も表現することも可能です。
まとめ・終わりに
今回、VLOOKUPの検索条件を完全一致ではなく、部分一致(含む)にする方法を紹介しました。
通常のVLOOKUPの検索では、検索キーと検索範囲のセルが完全一致したものが抽出されます。
しかし、VLOOKUPの検索キーの引数指定時に、アスタリスク(*)で検索ワードを挟むと、部分一致で抽出することが可能になります。
この方法を応用すると、前方一致や後方一致というパターンも表現可能です。
VLOOKUPを使う場合に、部分一致のほうがオートフィルを利用できて数式を書く手間が省けたり、見やすい場合があります。
VLOOKUPで検索キーを含んでいる行を抽出したい場合に、今回紹介した方法を試してみて下さい。