検索について教えてください。

質問

セルA　　　B　　　C　　　　D　　　　E　　　F　　　　G　　　　H　　　　

日付　　　　入庫数　ロットNo　在庫　　　　　日付　　出庫数　ロットNo　在庫　
07/03/01 　100 　　 080101 　　100 　　07/03/02 　　 50 　　　080102 　50
07/03/01 　100 　 　080102 　100 07/03/06 30 080105 20
07/03/05 50 080105 　 50 07/03/07 20 080101 80
　 07/03/07 50 080102 0
. .
. .
. .
. .
07/03/30 250 080115 07/03/31 200 080115 50

と、上記のような時、セル「I」にロットNoを入力すると
セル「J」に数量が出る方法を教えてください。

例えば
　セル「I」に080102と入力するとセル「J」に「0」と出力される。
また、セル「I」に080101と入力するとセル「J」に「80」と出力される。

わかる方、教えてください。

回答No.4

maron--5 · ベストアンサー

◆No1,No2のmaronです
◆すみませんでした、訂正します

J2=IF(COUNTIF(C2:G100,I2),INDEX((H1:H100,D1:D100),SUMPRODUCT(N(MAX((MAX((C2:G100=I2)*A2:E100)=A2:E100)*(C2:G100=I2)*ROW(A2:A100)))),1,IF(INDEX(G1:G100,SUMPRODUCT(N(MAX((MAX((C2:G100=I2)*A2:E100)=A2:E100)*(C2:G100=I2)*ROW(A2:A100)))))=I2,1,2)),"")

★この式は「配列数式」です。式を入力後、Ctrl＋Shift＋Enter　をおして、式を確定させてください。
★確定すると、式の両端に｛　｝がつきます。

回答No.3

maron--5

◆先程の回答は、誤りでしたので無視してください

回答No.2

maron--5

◆これでいかがでしょうか？
★#1さん同様、「入庫、出庫で最終日が同じなら出庫側の在庫を優先して表示します」
　　　　A　　　　　　B　　　　　C　　　　　　D　　　　　E　　　　　　F　　　　　　G　　　　　H　　　　　　I　　　　　　J
1　　日付　　　入庫数　　ロットNo　　在庫　　　日付　　　出庫数　　ロットNo　　在庫
2　　07/3/1　　　100　　　080101　　　100　　　07/3/2　　　　50　　　080102　　　50　　　　080101　　　　80
3　　07/3/1　　　100　　　080102　　　100　　　07/3/6　　　　30　　　080105　　　20
4　　07/3/5　　　 50　　　080105　　　 50　　　07/3/7　　　　 20　　　080101　　　80
5　　　　　　　　　　　　　　　　　　　　　　　　　　　　07/3/7　　　　50　　　080102　　　0

J2=IF(COUNTIF(C2:G100,I2),INDEX((H1:H100,D1:D100),SUMPRODUCT(N(MAX(((C2:G100=I2)*ROW(A2:A100))))),1,IF(INDEX(G1:G100,SUMPRODUCT(N(MAX(((C2:G100=I2)*ROW(A2:A100))))))=I2,1,2)),"")

回答No.1

zap35

この質問を拝見するのは３回目に思います。すみませんが、毎回肝心なことは書かれていませんね。今回もＪ列にはどのようなルールで数値を検索するのかが説明されていません。screamsさんには自明のことでも、第三者は分からないことなのです。

想像を働かせると、80101というロットナンバーが入庫欄（Ａ～Ｄ列）、出庫欄（Ｅ～Ｈ列）の両方に現れるとき、日付の新しい方の在庫数をＪ列に表示するという意味ですか？

また私の想像が当たっているなら、Ｉ列に指定したロットの最終入庫日と最終出庫日が同じ日だったら入庫欄の在庫、出庫欄の在庫のどちらをＪ列に表示すればよいのでしょう。

とりあえず式を書きます。入庫、出庫で最終日が同じなら出庫側の在庫を優先して表示します。検索するロットナンバーはＩ２セルにあるものとします。最初は式を分割して説明します

最終入庫日は
　=INDEX($A$1:$A$500,MAX(INDEX(($C$2:$C$500=I2)*ROW($C$2:$C$500),)))
その時の在庫（入庫側）は
　=INDEX($D$1:$D$500,MAX(INDEX(($C$2:$C$500=I2)*ROW($C$2:$C$500),)))

最終出庫日は
　=INDEX($E$1:$E$500,MAX(INDEX(($G$2:$G$500=I2)*ROW($G$2:$G$500),)))
出庫側の在庫数は
　=INDEX($H$1:$H$500,MAX(INDEX(($G$2:$G$500=I2)*ROW($G$2:$G$500),)))
になります。
あとは日付が大きい方を判断して在庫数をＪ列に求めれば良いのですが、これを一つの式にすると以下になります。

=IF(AND(COUNTIF($C2:$C500,I2)=0,COUNTIF($G2:$G500,I2)=0),"対象なし",IF(IF(COUNTIF($C2:$C500,I2)>0,INDEX($A$1:$A$500,MAX(INDEX(($C$2:$C$500=I2)*ROW($C$2:$C$500),))),0)>IF(COUNTIF($G2:$G500,I2)>0,INDEX($E$1:$E$500,MAX(INDEX(($G$2:$G$500=I2)*ROW($G$2:$G$500),))),0),INDEX($D$1:$D$500,MAX(INDEX(($C$2:$C$500=I2)*ROW($C$2:$C$500),))),INDEX($H$1:$H$500,MAX(INDEX(($G$2:$G$500=I2)*ROW($G$2:$G$500),)))))

式が長くなってしまってすみません。データが500行以上あるなら範囲は置換してください