EXCEL VLOOKUPで含む検索

質問

＿| 　　　Ａ　　　 |　　Ｂ　 　| Ｃ　 | | Ｚ |
1 |赤いイチゴ　|みかん　|\30　| | 　 |
2 |　　　　　　　　|リンゴ　　|\50　| | 　 |
3 |　　　　　　　　|イチゴ　 |\80　| | 　 |

A1に検索値（例：赤いイチゴ）を入力すると、検索範囲（B:C列）のっている単語が含まれていれば値段をZ1に表示したいのですが、うまくいきません。
検索値が一致ならば、=VLOOKUP(A1,$B:$C,1,FALSE)だとおもうのですが、=VLOOKUP("*"&A1&"*",$B:$C,1,FALSE)だと#N/Aエラーが出ます。

どなたか、知恵をお貸しください。

回答No.6

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

Noubleさん補足 どうもです <(_ _)>

検索して見つからない場合に問題があるので修正させていただきます
=IF(COUNT(INDEX(FIND(B1:B3,A1),))<>1,"",
INDEX(C1:C3,MAX(INDEX(ISNUMBER(FIND(B1:B3,A1))*ROW(B1:B3),))))
複数見つかった場合(リンゴ味のイチゴ)は非表示としました。

回答No.5

Nouble

CoalTarさん助としますね
=IF(A1="","",INDEX(C1:C3,MAX(ISNUMBER(INDEX(FIND(B1:B3,A1),0,0))*ROW(B1:B3))))
と数式を変更すると
Ctrl+ShiftをおしながらEnterしなくても値が表示されます
同様に
=SUMPRODUCT(ISNUMBER(FIND($B$1:$B$3,A1,1))*($C$1:$C$3))も
=SUMPRODUCT(ISNUMBER(index(FIND($B$1:$B$3,A1,1),0,0))*($C$1:$C$3))
とすれば
ただエンターするだけで数値が表示されますよ

実はこれは古くからある仕様という名のバグでして
配列数式として出された演算結果がより下位レベルネストの関数に正しく受け継がれない場合があるのです

こういった場合数式バー上にカーソルを入れておいて
Ctrl+ShiftをおしながらEnterをすればこういったデータ受け渡しエラーは回避できるのですが
そんなことをしなくてもindex(配列数式,0,0,)によって
配列数式全体の値の配列を返させれば
その後の処理は上手く行くので
上記のように普通にエンターするだけで良いようになるのです

このことはINDEXのヘルプ中の0値を指定した場合の解説に
確かに凄く分かり難くではありますが
記載されていますよ

回答No.4

tom04

こんばんは！
横からお邪魔します。

お示しの表の配置でZ1セルに
=IF(A1="","",INDEX(C1:C3,SUMPRODUCT((ISNUMBER(FIND(B1:B3,A1)))*ROW(A1:A3))))

これは配列数式ではありませんので、
この画面からそのままZ1セルにコピー＆ペーストしてみてください。
たぶん大丈夫だと思います。

尚、検索範囲が異なれば当然数式の範囲指定も変わってきますし、
空白が含まれば場合はもう少し複雑になると思います。

以上、参考になれば幸いです。m(__)m

回答No.3

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

「リンゴ味のイチゴ」の場合はどのように対処するのでしょう。
表の下方を優先するとして
Z1セルに
=IF(A1="","",INDEX(C1:C3,MAX(ISNUMBER(FIND(B1:B3,A1))*ROW(B1:B3))))
[Ctrl]+[Shift] ＋[Enter] で確定、配列数式です({}で囲まれる)

参考まで

お礼

前後両方に検索単語がはいることを想定していませんでした。
しかし、やっぱり[Ctrl]+[Shift] を同時におしつつ[Enter]で貼り付け確定ができないので、エラーが出ます。

せっかくのアドバイスでしたが、私の実力不足ゆえに役立てず、すいません。
ありがとうございました

回答No.2

mshr1962

=SUM(IF(ISNUMBER(FIND($B$1:$B$3,A1,1)),$C$1:$C$3))
と入力してCtrl+Shift+Enterで配列数式
または
=SUMPRODUCT(ISNUMBER(FIND($B$1:$B$3,A1,1))*($C$1:$C$3))

お礼

なぜかCtrl+ShiftをおしながらEnterで貼り付けることができません・・・excel2007なのですが。

下の数式を参考にさせていただきます。
ありがとうございました。

回答No.1

keithin

昨日同じご相談がありましたので下記に参考リンクを貼っておきます。参考にしてみてください。

＃回答で「前方一致」と注釈していましたが，部分一致でも大丈夫な式に落ち着いていました。

参考URL：

http://oshiete1.nifty.com/qa5746352.html

お礼

私の探し方が未熟だったようです。
過去回答からアドバイスを探していただき、ありがとうございました