エクセルのFIND関数の複数セル参照について

質問

C4=LOOKUP(0,0/FIND(B4,テーブル1[アドレス]),テーブル1[名称])

　「テーブル1」の「アドレス」列を部分一致で検索し、「B4」セルの文字列に該当する項目があったら、「テーブル1」の「名称」列のデータを「C4」セルに表示するよう、インターネット上の情報を参照しながら数式を組んでいます。
　ところが、「B4」セルの文字列に該当する項目があるときはいいのですが、該当項目がない場合は「#N/A」と表示されるため、「IF」関数を追加し、以下のようにしたところ、うまく動きませんでした。

C4=IF(COUNT(FIND(B4,テーブル1[アドレス])),LOOKUP(0,0/FIND(B4,テーブル1[アドレス]),テーブル1[名称]),"未使用")

　「数式の検証」で調べたところ、「LOOKUP」関数内の「FIND」関数は「テーブル1」の「アドレス」列の各セルをそれぞれ参照しているのに対して、「COUNT」関数内の「FIND」関数は同じ行（C4の場合は4）の「テーブル1」の「アドレス」列しか参照していませんでした。

「LOOKUP」関数内の「FIND」関数のテーブル1[アドレス]: {"アドレス1";"アドレス2";"アドレス3"}
「COUNT」関数内の「FIND」関数のテーブル1[アドレス]: "アドレス4"

　参照内容が異なるのはなぜでしょうか。
　この場合、どのようにすればよろしいのでしょうか。
　また、「LOOKUP」関数は「旧バージョンとの互換性を維持するためのもの」となっており、代わりに「VLOOKUP」や「HLOOKUP」、「MATCH」関数などで代用はできないのでしょうか。

　何卒ご教示よろしくお願いいたします。

回答No.3

keithin · ベストアンサー

回答を投稿してから部分一致に気がつきました。

そのときは
=IFERROR(VLOOKUP("*"&B4&"*",G:H,2,FALSE),"未使用")
=IFERROR(INDEX(G:G,MATCH("*"&B4&"*",H:H,0)),"未使用")
のようにします。

お礼

ご教示いただきありがとうございます。
「"*"」という風に書くとそのままアスタリスクは文字として検索されるのかと思いきや、そうではなかったんですね！
大変勉強になりました。ありがとうございました。

回答No.2

keithin

ご利用のエクセルのバージョンがご相談に書かれていませんが，情報から２０１０以降を利用しているのが明らかです。

とりあえずご質問の数式をそのまま活かしたいなら
=IFERROR(LOOKUP(0,0/FIND(B4,テーブル1[アドレス]),テーブル1[名称]),"未使用")
とでもしてください。

普段使いの関数を使い
アドレス列（仮にＧ列）のひとつ右隣(仮にＨ列)に名称列があるなら
=IFERROR(VLOOKUP(B4,G:H,2,FALSE),"未使用")
としたほうが，遙かに簡単です。

リストの配置をどうしても動かしたくないなら
仮にＨ列を検索してＧ列を取得する
=IFERROR(INDEX(G:G,MATCH(B4,H:H,0)),"未使用")
です。

お礼

ご回答ありがとうございます！
質問内容からOfficeのバージョンがお分かりになられるとは、流石です。

=IFERROR(LOOKUP(0,0/FIND(B4,テーブル1[アドレス]),テーブル1[名称]),"未使用")
としたほうが遥かにスマートですね！ぜひそのようにさせていただきます^_^

　なぜLOOKUP(0,0/FIND(B4,テーブル1[アドレス]),テーブル1[名称])のような面倒な書き方になっているのかといいますと、部分一致でないと照合しないデータが含まれているからです。
　ご教示の方法で「LOOKUP」関数を「VLOOKUP」や「INDEX」関数に置き換えてしまいますと、完全一致となり、一部のデータが誤って「未使用」となってしまいます。

回答No.1

某HN クロメート(Chromate)（@CoalTar）

複数一致するときに、どこの値が返ってくるか安定しないけどいいのかなあ
とか心配しつつ

FIND(B4,テーブル1[アドレス])
は、ヘルプに載っているような基本的な使用方法ではなく、配列として返しています。
そのままでは配列として認識していません。INDEX関数やLOOKUP関数など、配列を扱う関数なら、配列として扱われますが、COUNT関数は、セル範囲として扱う関数になっています。

で、配列として認識させるためには、
=IF(COUNT(FIND(B4,テーブル1[アドレス])),LOOKUP(0,0/FIND(B4,テーブル1[アドレス]),テーブル1[名称]),"未使用")
[Ctrl]+[Shft] ＋[Enter] で確定、配列数式。{ }で挟まれる。

または、配列として認識させるよう
=IF(COUNT(INDEX(FIND(B4,テーブル1[アドレス]),)),
LOOKUP(0,0/FIND(B4,テーブル1[アドレス]),テーブル1[名称]),"未使用")
といった方法もあります。

お礼

ご教示いただいた方法で無事に配列として認識させることができました。ありがとうございます！
「テーブル1[アドレス]」にはゴミデータが入っていて、部分一致でないと照合しませんが、重複することはないのでご心配なく^_^