Excel。検索する値の一部だけを含む表から対応する値を抽出したい
＜IFERROR+LOOKUP+FIND関数＞
何をしたいのかを、まずは確認しておきましょう。
A列の住所の文字列。
例えば、A2に横浜市という文字が含まれています。
横浜市という文字が含まれていたら、D1;E4までの担当者リストから、横浜市の担当である。内藤さんの名前をB2に表示するという作業です。
ワイルドカードをつかうのか？VLOOKUP関数をつかうのか？
あれやこれやと考えると思います。
ただ、VLOOKUP関数では、解決しないことは、すぐにわかります。
では、B2には、どのような数式が設定しているのか、確認しましょう。
=IFERROR(LOOKUP(0,0/FIND($D$2:$D$4,A2),$E$2:$E$4),"")
最初のIFERROR関数をつかっているのは、該当するデータが無い場合「#N/A」エラーが表示されるので、エラー表示を空白にするようにするために使っています。
わかりにくいのが、LOOKUP関数ですが、先にFIND関数から説明します。
D列の地区名のデータが、A2の住所の文字内に含まれているのかを確認します。
このケースでは、5,#VALUE!,#VALUE!と算出されます。
5と算出されたのは、A2のセルに横浜市という文字が、左から5文字目にあることを意味しています。
そして、LOOKUP関数。
今回は、LOOKUP(検索値,検索範囲,対象範囲)という引数をもっている、ベクトル形式のLOOKUP関数で処理をします。
最初の検索値は、「0」。
次の引数の「検索範囲」から「0」をみつけるためのものです。
検索範囲は、0を先程紹介したFIND関数で算出した値で除算させます。
なぜこのようなことをしているのかというと、FIND関数で見つからなかったものはエラーと算出されています。
「0/エラー」と計算させたところで、結果はエラーになるわけです。
今回は、「0」を探していますので、エラーと算出されているものを除外することができるというわけです。
逆に、今回のように「5」という算出結果が出ているのであれば「0/5」となり「0」という結果を算出します。
こうすることで、検索値が「0」のものをみつけることができるわけです。
最後の引数の対象範囲には、「$E$2:$E$4」と担当列を設定します。
オートフィルで数式をコピーするので、絶対参照も忘れずに設定しておきます。
これで、「0」の結果の行を抽出することになるので、担当の列に、それぞれの担当者名を表示することができるというわけです。
なかなか、使う機会が少ない、「LOOKUP関数」ですが、VLOOKUP関数でもダメ、HLOOKUP関数でもダメというケースでは、意外と活躍する関数なので、LOOKUP関数も抑えておくといい関数なのかもしれませんね。
