excelで複数条件を抽出して反映させる方法

質問

sheet1 ＜基データの表＞
セルA3:登録番号
セルI3:使用先会社名　

sheet2＜sheet1を反映させvlookup設定で抽出した完成表＞
セルB2:登録番号
セルC2:使用先名称

現在、上記のようなセル設定をしており、
sheet2のセルC2にvlookup関数を設定し、B2に登録番号を入力すると、
sheet1の基データから条件を抽出し使用先名称を表示させています。

しかし、基データの登録番号には重複するものが何件かあり、
その場合は最初の値しか返さないので、間違ったデータが抽出されてしまうことがあります。
この問題を解決する方法はありませんか？

(1)vlookup関数以外を使用することになっても大丈夫です。
(2)できれば、重複するものはsheet2に反映される時点で両方反映されるとよいのですが、それができなくても、重複していることがわかればよいです。
(3)登録番号以外をデータの抽出に使用することはできないので、これは変更できません。

以上、お手数ですがご教授くださると幸いです。

回答No.3

MackyNo1

＞しかし、基データの登録番号には重複するものが何件かあり、
その場合は最初の値しか返さないので、間違ったデータが抽出されてしまうことがあります。
この問題を解決する方法はありませんか？

少し複雑な配列数式になりますが、１つの数式だけで複数の該当データを一覧表示することができます。

元データがSheet1のA3セル以下に入力されている場合、ご希望の登録番号に合致する複数のデータをC2セルから右方向に表示するなら、たとえばC2セルに以下のような関数を入力し、右方向に適当数および下方向にオートフィルすることになります。

=INDEX(Sheet1!$I:$I,SMALL(INDEX((Sheet1!$A$3:$A$100<>$B2)*100+ROW($A$3:$A$100),),COLUMN(A1)))&""

回答No.2

mu2011

一例です。
重複した場合、★マークを付けるようにしました。
=IF(COUNTIF(Sheet1!A:A,B2),IF(COUNTIF(Sheet1!A:A,B2)>1,"★"&VLOOKUP(抽出式),VLOOKUP(抽出式)),"")

お礼

初心者の私にもとても単純明快でわかりやすく、早速試してうまくいきました。今後別のデータ作成にも利用させていただきます。どうもありがとうございました！

回答No.1

tom04

こんにちは！
外しているかもしれませんが・・・

↓の画像で説明させていただきます。
とりあえず、B2セルに登録番号が表示されれば、D列にその番号の会社名を表示させる方法です。

Sheet1に作業用の列を使わせてもらっています。
作業列J3セルに
=IF(A3=Sheet2!$B$2,ROW(A1),"")
として、オートフィルで下へずぃ～～～！っとコピーします。

そして、Sheet2のD2セルに
=IF(OR($B$2="",COUNT(Sheet1!$J$3:$J$100)<ROW(A1)),"",INDEX(Sheet1!$I$3:$I$100,SMALL(Sheet1!$J$3:$J$100,ROW(A1))))
という数式を入れ、オートフィルで下へコピーすると
画像のような感じになります。
とりあえず、Sheet1のデータは100行目まで対応できるようにしていますが、
データ量によって範囲指定の領域はアレンジしてください。

以上、参考になれば幸いですが、
他に良い方法があれば読み流してくださいね。m(__)m

お礼

ご丁寧にありがとうございます！
これまで自分が考えたことのない方法でしたので、とても勉強になります。すぐにこのような発想が思い浮かぶなんて、本当にすごい。今後のデータ作成時に活用させていただきます。
どうもありがとうございました。