質問

EXCEL2003で、Vlookupの検索で複数ヒットした場合全て抽出したいんですが、可能ですか?

<sheet1>
  A列   B列
1 A1000 1234
2 A2000 2345
3 A3000 3456
4 A1000 7777
5 A4000 4567
6 A1000 8888

<sheet2>
B1に関数を入れて、
  A列   B列
1 A1000  1234・7777・8888

のように表示させたいんです。


過去質問を確認すると、imogasi方式や、オートフィルタを使用するとありましたが、
imogasi方式はいまいち理解ができず(っというか、有効な手段を見つけることができませんでした)
オートフィルタは使用上、今回は有効な手段ではありません。
また、Vlookupには拘りません。他の関数や、複数の関数を組み合わせても大丈夫です。
関数を使ってできるようであればご教示お願いします。
(複雑な式になっても構いません)

よろしくお願いします。

通報する

回答 (4件)

該当データの出現個数が一定数であれば関数の組み合わせや作業セルを多用することで対処可能ですが、計算式はとて無複雑なものになります。

出現個数が未知数の場合は想定範囲外の個数データーがあった場合は正しい結果にはなりません。

VBAを利用した抽出方法が適していますが、マクロは利用可能な環境でしょうか?

この回答へのお礼

回答遅くなり申し訳ございません。
確かにVBAであれば適切なのでしょうが、訳あってマクロ使用ができない状態でした。

ありがとうございました。

VLOOKUP関数でできないこともないでしょうがかなり複雑になりますね。
次のように作業列を作って対応します。
シート1のC1セルに次の式を入力して下方にオートフィルドラッグします。

=IF(A1="","",A1&"/"&COUNTIF(A$1:A1,A1))

次にシート2のB1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNTIF(Sheet1!C:C,A1&"/"&1)=0,"",INDEX(Sheet1!B:B,MATCH(A1&"/"&1,Sheet1!C:C,0)))&IF(COUNTIF(Sheet1!C:C,A1&"/"&2)=0,"","・"&INDEX(Sheet1!B:B,MATCH(A1&"/"&2,Sheet1!C:C,0)))&IF(COUNTIF(Sheet1!C:C,A1&"/"&3)=0,"","・"&INDEX(Sheet1!B:B,MATCH(A1&"/"&3,Sheet1!C:C,0)))&IF(COUNTIF(Sheet1!C:C,A1&"/"&4)=0,"","・"&INDEX(Sheet1!B:B,MATCH(A1&"/"&4,Sheet1!C:C,0)))&IF(COUNTIF(Sheet1!C:C,A1&"/"&5)=0,"","・"&INDEX(Sheet1!B,B,MATCH(A1&"/"&5,Sheet1!C:C,0)))

この式では複数のデータが5個まであるケースに対応しています。

この回答へのお礼

勉強になりました!
ありがとうございました!!!

VLOOKUP関数だけで対応するなら以下のようなVLOOKUP数式で2つ目以降のデータを表示することができます(C1セルに検索値で。

2つ目のデータ
=VLOOKUP(C1,OFFSET($A$1,MATCH(C1,$A:$A,0),0,100,2),2,0)

3つ目のデータ
=VLOOKUP(C1,OFFSET(A3,MATCH(C1,A:A,0)+MATCH(C1,OFFSET(A1,MATCH(C1,A:A,0),0,100,1),0),0,100,2),2,0)

この数式をIF関数を使ってつなげれば1つのセルに検索結果を表示することが可能です。

配列数式を使うなら以下のような関数になります(A列のデータ範囲に「データ」と名前が付けてある場合)

=SUBSTITUTE(TRIM(VLOOKUP(C1,A:B,2,0)&" "&INDEX(B:B,SMALL(INDEX((データ<>C1)*1000+ROW(データ),),2))&" "&INDEX(B:B,SMALL(INDEX((データ<>C1)*1000+ROW(データ),),3))&" "&INDEX(B:B,SMALL(INDEX((データ<>C1)*1000+ROW(データ),),4)))," ","・")

上記の数式は配列を多用していますので、入力セルが多くなると動きが重くなるので、上のVLOOKUP式などでエラー処理をしない数式などと適宜使い分けてください。

この回答へのお礼

こちらも勉強になりました!
ありがとうございました!!

こんばんは!
VLOOKUP関数で複数のデータは抽出できないと思います。
VLOOKUP関数とMATCH関数での検索は出来ますが、一番上のデータしかヒットしないはずです。

そしてヒットした複数データを一つのセルに表示させたいということですが
これも関数では無理だと思います。

ただし、一つの案として↓の画像のように列方向にヒットしたものを表示させることは可能です。

Sheet2のB2セルに
=IF($A2="","",IF(COUNTIF(Sheet1!$A$2:$A$1000,$A2)<COLUMN(A1),"",INDEX(Sheet1!$B$2:$B$1000,SMALL(IF(Sheet1!$A$2:$A$1000=$A2,ROW($A$1:$A$999)),COLUMN(A1)))))

(Sheet1の1000行目まで対応できる数式にしています。)
これは配列数式になってしまいますので、
この画面からSheet2のB2セルに貼り付け後、F2キーを押す、又はB2セルでダブルクリック、又は数式バー内で一度クリックします。
編集可能になりますので
Shift+Ctrlキーを押しながらEnterキーで確定してみてください。
数式の前後に{ }マークが入り配列数式になります。

これを列方向と行方向にオートフィルでコピーすると
画像のような感じになります。

どうしもて一つのセルに納めたいのであれば、
これを CONCATENATE関数か & でまとめていくくらいしか思い浮かびません。

以上、参考になればよいのですが
的外れなら読み流してくださいね。m(__)m

この回答へのお礼

図解説明までして頂き、大変感謝します!
このような回答方法であれば、分かりやすくて助かります。
内容としては、求めていたものとは違い残念ですが、
とても勉強になりました。ありがとうございました。

このQ&Aは役に立ちましたか?15 件

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

新しく質問する

このQ&Aを見た人はこんなQ&Aも見ています

注目の記事

フリーアナウンサー長谷川豊氏の新コーナー「このニュースどう思います?」がスタート!

元フジテレビのフリーアナウンサー長谷川豊氏から気になるニュースについておしトピの皆さんに質問します! 皆さんの意見をもとに長谷川豊氏がコラムを執筆します! アプリリリース記念として最大1万分のアマゾンギフト券プレゼントキャンペーンも実施中!


新しく質問する

このカテゴリの人気Q&Aランキング

毎日見よう!教えて!gooトゥディ

べんりQ&A特集