EXCEL2003で、Vlookupの検索で複数ヒットした場合全て抽出

質問

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には拘りません。他の関数や、複数の関数を組み合わせても大丈夫です。
関数を使ってできるようであればご教示お願いします。
（複雑な式になっても構いません）

よろしくお願いします。

回答No.3

KURUMITO · ベストアンサー

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

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

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

=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個まであるケースに対応しています。

お礼

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

回答No.4

web2525

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

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

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

お礼

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

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

回答No.2

MackyNo1

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式などでエラー処理をしない数式などと適宜使い分けてください。

お礼

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

回答No.1

tom04

こんばんは！
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

お礼

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