こんな関数ありますか？

質問

下記の通り表示させたいのですが、このように表示できる関数はありますか？

　　　　　Ａ列　　　　　　Ｂ列　　　　　Ｃ列
１行目　川田市　　　　　川越市　　　　201
２行目　秩父郡横瀬町　熊谷市　　　　202
３行目　熊谷市　　　　　川田市　　　　203
４行目　千代田区　　　　横瀬町　　　　361
５行目　横浜市西区　　児玉町　　　　382
６行目　川越市　　　　江南町　　　　402
　・　　　　・　　　　　・　　　　　・
　・　　　　・　　　　　・　　　　　・
　・　　　　・　　　　　・　　　　　・

こんな感じに870行目まで全国の市区町村名とＪＩＳコードが並んでいます。
その中で、Ａ１の文字列が、Ｂ列（１～870行目）の中にあれば、その行のＣ列の数字をＤ１に表示、含んでいなければＤ１に999と表示させたいのですが、どうすればよいのでしょうか？
ちなみに、Ａ列は検索のセルなので文字はいろいろ、Ｂ列・Ｃ列は参照のセルなので固定です。
上記の例でいくと、Ｄ１に203、Ｄ２に361、Ｄ３に202、Ｄ４に999といった感じです。
以前、こちらでSUMPRODUCT・COUNTIF・IF・ROW・INDEX関数を組み合わせたものを教えていただいたのですが、そのときはうまく出来たのですが、今回なぜかうまくいきません。

質問が分かりにくければ補足します。
どうぞ宜しくお願いします。

回答No.4

mshr1962 · ベストアンサー

=MIN(10^ISERROR(FIND($B$1:$B$6,A1))*($C$1:$C$6),999)
と入力してCtrl+Shift+Enterで配列数式にしてください。

お礼

できました～（泣）
ありがとうございました（泣）
これで仕事が進みます♪

回答No.5

mu2011

余りスマートでないのですが次の方法は如何でしょうか。
D1セルに=IF(SUMPRODUCT((RIGHT(A1,(LEN($B$1:$B$870)))=$B$1:$B$870)*(ROW($B$1:$B$870)))>0,INDEX($C$1:$C$870,SUMPRODUCT((RIGHT(A1,LEN($B$1:$B$870))=$B$1:$B$870)*(ROW($B$1:$B$870)))),999)を設定、D1セル枠の右下の■をダブルクリックして下さい。

お礼

ありがとうございます。
Ｎｏ４さんのものでできました。

回答No.3

EL-SUR

■D1セル
=IF(SUMPRODUCT(COUNTIF(A1,"*"&B$1:B$870&"*")),INDEX(C$1:C$870,MAX(INDEX(COUNTIF(A1,"*"&B$1:B$870&"*")*(ROW(B$1:B$870)),0))),999)

A列は検索用ならそれほどデータは多くないということですよね。
あまり多く入力すると処理が重くなります（それでなくても重いですが・・）

B列に重複する文字列はありませんか？
たとえば川越市と南川越市のように。
そういうのがあると正しい結果が返らない可能性があります。
その場合、B列の入力順は川越市→南川越市のように文字数の少ない方を上の行にしておけばいいかもしれません。

B列が何行目から始まっていても、ROW(B$1:B$870) はそのままにしておいてください。

お礼

ありがとうございます。
やってみましたがエラーが出てしまいました。
Ｎｏ４さんのもので解決できました。

回答No.2

noname#37676

D1セルに
=IF(ISERROR(VLOOKUP(A2,$B$2:$C$870,2,FALSE)),999,VLOOKUP(A2,$B$2:$C$870,2,FALSE))
Ｄ列にフィルコピーでは？？

お礼

ありがとうございます。やってみましたが、この方法だと、完全一致の際は正しく表示されるのですが、部分一致、例えば秩父郡横瀬町と書いてある中から横瀬町を検索して361と表示させるところが999になってしまいます。
もし原因がお分かりでしたら再度ご助言お願いします。

回答No.1

noname#25358

補足お願いします。

１．Ａ列にはＡ１、Ａ２、Ａ３にそれぞれ別々の文字列が入っているようですが、検索対象となるのはあくまで「Ａ１」ということでいいのでしょうか？
　だとすると、Ａ２以降のセルは使用しないということになりますが。

２．上記の例ではＤ１に２０３とありますが、Ｃ１は２０１です。
　これらの数字の間にはなぜ相関関係がないのでしょうか。

お礼

すみません、説明がわかりにくかったですよね。
補足しましたのでお手数ですがお願いします。

補足

１．Ａ１の文字列の一部又は全部がＢ１～８７０の中に含まれているか検索したいのです。従って、Ｄ１に表示させるのはＡ１を検索した結果です。Ｄ１に表示させるときはＡ２以下のＡ列は使用しません。また、Ｄ２以降、関数をコピーしてＡ２、Ａ３・・・の検索結果も出していきます。

２．Ａ１に川田市とありますので、Ｂ列から川田市がないか検索します。するとＢ３にありますので、その行のＣ列、つまりＣ３の203をＤ１に表示させたいです。従って、Ａ２は秩父郡横瀬町なので、Ｂ列から検索して秩父郡横瀬町の一部、横瀬町が入っているＢ４のＣ列、つまり361をＤ２に表示させる。という感じにしたいです。