Excel。VLOOKUP関数で先頭の複数文字と合致するデータを抽出したい
＜VLOOKUP＋LEFT+FIND関数＞
別表から合致するデータを検索し抽出することができる「VLOOKUP関数」。
例えば、商品コードと合致する商品名や単価などを検索し抽出することはできますが、次のような、先頭からハイフンまでの複数文字を別表から抽出するにはどのようにしたらいいのでしょうか。
VLOOKUP関数は、4番目の引数の「検索方法」を近似値か完全一致でなければなりません。
講座番号のハイフンまでの左の文字列と合致するものという場合には、VLOOKUP関数だけでは対応することができません。
そこで、B2の数式は、ちょっと長くなりますが、次のように設定することで、対応することができます。
=VLOOKUP(LEFT(A2,FIND("-",A2)-1),$A$8:$B$10,2,FALSE)
あとは、オートフィルで数式をコピーします。
完全一致は完全一致ではあるのですが、ハイフンまでというのを、どう表現すればいいのかを考えていきます。
VLOOKUP関数の最初の引数「検索値」をハイフンより前の文字列としてあげればいいわけです。
そこで、検索値には、LEFT関数とFIND関数をつかうことで、ハイフンよりも前の文字列を抽出することができます。
「LEFT(A2,FIND("-",A2)-1)」
ハイフンの位置が文字列の何文字目にあるのかを、算出すればいいわけなので、FIND関数をつかって、ハイフンの文字位置を確認します。
「EX-001」の場合は、「3」と算出されます。
ただし、この「3」のままLEFT関数で使ってしまうと、左から3文字目までを抽出ということになり「EX-」となってしまうので、検索することができません。
そこで、「-1」しているというわけです。
これで、「EX」というハイフンより前の文字列を抽出することができましたので、あとは、VLOOKUP関数を設定するだけで完成します。
なお、今回は、ハイフンで区切られていたので、ハイフンより左側の文字列を抽出するというルールがありましたが、区切られているルールがない場合は、この数式をつかうことはできません。