複数条件で検索し、検索結果が2個以上の場合について教えてください。

質問

複数条件で検索し、検索結果が2個以上の場合について教えてください。

たとえば、
検索値が商品コード：A5　商品名：電池
商品の使用期限が6月1だったり7月1日だったりと
同じ商品コードと商品名でも期限が違うものが存在します。
また、検索するのは別ファイルです。
コードと品名が一致したら検索結果（使用期限）をそれぞれ分けて出したいです。

例として、【検索値】コードがA5で商品名が電池
　　　　　【検索結果】6月1日と7月1日
【シート1】
商品コード　　商品名　　　検索結果1　　　検索結果2　　検索値3
A5　　　　　　　電池　　　　　6月1日　　　　　7月1日
B5　　　　　　　電球　　　　　8月1日
A5　　　　　　　薬　　　　　　 3月1日　　

【検索する別ファイルのシート1】

使用期限　　　商品コード　　商品名
6月1日　　　　　A5　　　　　　　電池
8月1日　　　　　B5　　　　　　　電球
3月1日　　　　　A5　　　　　　　薬
7月1日　　　　　A5　　　　　　　電池

VLOOKUP関数は1つの検索結果なので
悩んでおります。

宜しく御願いします。

回答No.6

tom04

No.1・5です！
何度もごめんなさい。

最初の画像の配列そのまま利用させていただいて、
↓の数式でなんとか作業用の列を使わずに可能だと思います。

=IF(COUNTBLANK($A2:$B2),"",IF(SUMPRODUCT(([Book2]Sheet1!$B$2:$B$1000=$A2)*([Book2]Sheet1!$C$2:$C$1000=$B2))<COLUMN(A1),"",INDEX([Book2]Sheet1!$A$2:$A$1000,SMALL(IF(([Book2]Sheet1!$B$2:$B$1000=$A2)*([Book2]Sheet1!$C$2:$C$1000=$B2),ROW($A$1:$A$999)),COLUMN(A1)))))

今回も配列数式になってしまいます。

No.4さんも仰っていらっしゃいますが
配列数式ではデータ量が多い場合はコンピュータにかなりの負担をかけますので
私個人的には作業用の列を使って少しでも式を短くし、配列数式を使わない方が良いと思います。

以上、お役に立てば良いのですが
何度もごめんなさいね。m(__)m

回答No.5

tom04

No.1です！
たびたびごめんなさい。

ご希望はBOOK2のSheetは手を加えたくない！ということですが
なかなか良い案が浮かびません。
苦肉の策ですが、前回の作業用の列を「非表示」にするくらいですかね！

作業列が表の途中に入るのが目障りであれば、A列を1列挿入し、それを作業列にします。
元データが右に1列ずつずれますので
前回の画像でのBOOK1のC2セルに数式は
=IF(COUNTBLANK($A2:$B2),"",IF(COUNTIF([Book2]Sheet1!$A$1:$A$1000,$A2&$B2)<COLUMN(A1),"",INDEX([Book2]Sheet1!$B$1:$B$1000,SMALL(IF([Book2]Sheet1!$A$1:$A$1000=$A2&$B2,ROW($A$1:$A$1000)),COLUMN(A1)))))

配列数式ですので、Shift＋Ctrlキーを押しながらEnterキーで確定です。

これを列方向と行方向にオートフィルでコピーします。

そして、A列全てを範囲指定　→　右クリック　→　「表示しない」を選択

この程度でごめんなさいね。m(__)m

回答No.4

MackyNo1

＞Book2のシートには手を加えずに出来る方法ってあるのでしょうか？

数式を多用すると動きが重くなりますが、補助列を使わない方法は以下のような式になります。

=IF(SUMPRODUCT(([Book2]Sheet1!$B$2:$B$1000=$A2)*([Book2]Sheet1!$C$2:$C$1000=$B2))<COLUMN(A1),"",INDEX([Book2]Sheet1!$A:$A,SMALL(INDEX(([Book2]Sheet1!$B$2:$B$1000&[Book2]Sheet1!$C$2:$C$1000<>$A2&$B2)*1000+ROW($A$2:$A$1000),),COLUMN(A1))))

回答No.3

imogasi

これは何のソフトの質問ですか。読みすすめるうちにExcelの質問らしいが、表題や始めにはっきり書くこと。
ーーー
検索といっているが、検索というより、表の組み換えの質問ではないか。
なぜなら
＞検索値】コードがA5で商品名が電池
なら
結果は
商品コード　　商品名　　　検索結果1　　　検索結果2　　検索値3
A5　　　　　　　電池　　　　　6月1日　　　　　7月1日
で、そのあとは要らない場合の話では。
だから
＞VLOOKUP関数は1つの検索結果
VLOOKUP関数の特質を知らない、見当ハズレのこと。
ーーー
関数による抜き出しは、既出回答の関数式のように、エクセル関数マニア向けで、意味も初心者には判りにくいと思うし、配列数式になっていたりするので、複雑で、関数でやるのは不適なタイプだ。
ーーー
質問者が出来るなら、VBAでやるのが良かろうと思う。
上記のように、表の組み換えなら
（１）商品コード　　商品名の２列で全データをソート
（２）商品コード＋商品名について、前行と変わらない間は、使用期限だけをC列から右列に、順次記していく。
列ポインタ変数を使って、書き込んだらその列の右列（＋１すればよい）を記憶する。
次の行の処理にうつる。
（３）商品コード＋商品名について、前行と変わった場合は、１行下に、A列商品コード、B列商品名を記し、C列にはその分の使用期限を採ってきて記す。
VBAの経験あって、コード例を知りたいなら、補足するが。
ーーー
参考までに、操作になるが
A列＋B列でユニークな（重複なく、漏れの無い）組み合わせを、別列に出す方法を書いておく。
例データ
A-C列
コードA コードB データ コードA コードB コードA コードB
a ｇ x a ｇ
a ｈ y a ｈ
a ｇ z b ｆ
b ｆ u b ｇ
b ｇ v c ｈ
b ｇ s c i
c ｈ t
c i r
E,F列に
コードA コードBという風にA、B列の見出しを作っておく。
G,H列にもコードA コードBという風にA、B列の見出しを作っておく。
データーフィルターフィルタオプションの設定
抽出先　指定した範囲
リスト範囲　$A$1:$C$9
検索条件範囲　$D$1:$E$1
抽出範囲　$G$1:$H$9
重複するレコードは無視するにチェックーOK
結果
A列＋B列を組み合わせて考えて
G,H列の組み合わせが、重複なく、漏れの無い組み合わせである。

回答No.2

KURUMITO

Ｂｏｏｋ2のシート1に別ファイルのデータがあるとします。
このシート1のＤ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(B2="","",B2&C2&"/"&COUNTIF(D$1:D1,B2&C2&"*")+1)

次にお望みにも表ですがＢｏｏk1のシートのＡ1セルに商品コード、Ｂ1セルに商品名の項目名があり、さらに右の列には検査結果1などの項目名があるとします。
そこでＣ2セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(COUNTIF('[Book2.xlsx]Sheet1'!$D:$D,$A2&$B2&"/"&COLUMN(A1))=0,"",INDEX('[Book2.xlsx]Sheet1'!$A:$A,MATCH($A2&$B2&"/"&COLUMN(A1),'[Book2.xlsx]Sheet1'!$D:$D,0)))

この式でＢｏｏｋ2の拡張子が違っているかもしれません。実際に別のブックのシートの該当箇所をクリックして正しいシート名になるようにしてください。

回答No.1

tom04

こんばんは！
一例です。
↓の画像で説明させていただきます。

下側がBOOK2で、それをBOOK1に表示するようにしてみました。

元データのBOOK2に作業用の列を設けています。
作業列D2セルは
=B2&C2
としてオートフィルで下へずぃ～～～！っとコピーします。

そして、BOOK1のC2セルに
=IF(COUNTBLANK($A2:$B2),"",IF(COUNTIF([Book2]Sheet1!$D$2:$D$1000,$A2&$B2)<COLUMN(A1),"",INDEX([Book2]Sheet1!$A$2:$A$1000,SMALL(IF([Book2]Sheet1!$D$2:$D$1000=$A2&$B2,ROW($A$1:$A$999)),COLUMN(A1)))))

これは配列数式になってしまいますので、
この画面からC2セルにコピー＆ペーストしただけではエラーになると思います。
C2セルに貼り付け後、F2キーを押す、又はC2セルでダブルクリック、又は数式バー内で一度クリックします。
編集可能になりますので
Shift＋Ctrlキーを押しながらEnterキーで確定してください。
数式の前後に｛　｝マークが入り配列数式になります。

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

尚、数式はBOOK2の1000行目まで対応できるようにしています。

以上、参考になれば良いのですが
外していたらごめんなさいね。m(__)m

補足

画像まで付けて説明して頂き有難うございました。
私の求めていた通りに検索でき、凄く参考になりました。

しかし私の説明不足で大変申し訳ありません。
Book2のシートには手を加えずに出来る方法ってあるのでしょうか？
宜しく御願いします。