Find all matches for multiple conditions

[kikiarain]

Hi,

I am trying to find INVOICE ID for all Invoices shipped for each SKU after a certain cut off date. I have a list of over 1000 SKUs with multiple cut off dates for each SKU in Sheet1. The invoice shipments data along with the dates is in Sheet2. I have tried to create a Pivot table and used INDEX-MATCH formula with small function but that is not working. Please advise if there's a VBA code or Excel formula I can use to handle this?


Sheet1
[TABLE="width: 208"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]SKU[/TD]
[TD]Cut off date[/TD]
[/TR]
[TR]
[TD]AX[/TD]
[TD="align: right"]10/30/2014[/TD]
[/TR]
[TR]
[TD]AX[/TD]
[TD="align: right"]11/20/2014[/TD]
[/TR]
[TR]
[TD]AX[/TD]
[TD="align: right"]12/17/2014[/TD]
[/TR]
[TR]
[TD]AX[/TD]
[TD="align: right"]12/22/2014[/TD]
[/TR]
[TR]
[TD]AX[/TD]
[TD="align: right"]2/19/2015[/TD]
[/TR]
[TR]
[TD]AX[/TD]
[TD="align: right"]4/23/2015[/TD]
[/TR]
[TR]
[TD]AX[/TD]
[TD="align: right"]5/1/2015[/TD]
[/TR]
[TR]
[TD]AX[/TD]
[TD="align: right"]5/13/2015[/TD]
[/TR]
[TR]
[TD]AX[/TD]
[TD="align: right"]12/30/2014[/TD]
[/TR]
[TR]
[TD]CRE[/TD]
[TD="align: right"]3/21/2015[/TD]
[/TR]
[TR]
[TD]CRE[/TD]
[TD="align: right"]4/1/2015[/TD]
[/TR]
[TR]
[TD]DR[/TD]
[TD="align: right"]9/11/2014[/TD]
[/TR]
[TR]
[TD]DR[/TD]
[TD="align: right"]3/11/2015[/TD]
[/TR]
[TR]
[TD]DR[/TD]
[TD="align: right"]5/7/2015[/TD]
[/TR]
[TR]
[TD]DR[/TD]
[TD="align: right"]12/4/2014[/TD]
[/TR]
[TR]
[TD]DR[/TD]
[TD="align: right"]1/29/2015[/TD]
[/TR]
[TR]
[TD]DR[/TD]
[TD="align: right"]3/20/2015[/TD]
[/TR]
</tbody>[/TABLE]


Sheet2

[TABLE="width: 320"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Part[/TD]
[TD]invoice_date[/TD]
[TD]invoice_id[/TD]
[/TR]
[TR]
[TD]AX[/TD]
[TD]9/4/2014[/TD]
[TD]INV4113[/TD]
[/TR]
[TR]
[TD]AX[/TD]
[TD]9/5/2014[/TD]
[TD]INV4118[/TD]
[/TR]
[TR]
[TD]AX[/TD]
[TD]9/8/2014[/TD]
[TD]INV4125[/TD]
[/TR]
[TR]
[TD]AX[/TD]
[TD]9/9/2014[/TD]
[TD]INV4142[/TD]
[/TR]
[TR]
[TD]DR[/TD]
[TD]1/9/2015[/TD]
[TD]INV4143[/TD]
[/TR]
[TR]
[TD]DR[/TD]
[TD]3/25/2015[/TD]
[TD]INV4149[/TD]
[/TR]
[TR]
[TD]DR[/TD]
[TD]4/11/2015[/TD]
[TD]INV4145[/TD]
[/TR]
[TR]
[TD]CRE[/TD]
[TD]9/11/2014[/TD]
[TD]INV4150[/TD]
[/TR]
[TR]
[TD]CRE[/TD]
[TD]4/11/2015[/TD]
[TD]INV4151[/TD]
[/TR]
[TR]
[TD]CRE[/TD]
[TD]5/12/2015[/TD]
[TD]INV4154[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Sheet2 (data)

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]Part[/td][td]invoice_date[/td][td]invoice_id[/td][/tr]

[tr][td]

2​

[/td][td]AX[/td][td]

9/4/2014​

[/td][td]INV4113[/td][/tr]

[tr][td]

3​

[/td][td]AX[/td][td]

9/5/2014​

[/td][td]INV4118[/td][/tr]

[tr][td]

4​

[/td][td]AX[/td][td]

9/8/2014​

[/td][td]INV4125[/td][/tr]

[tr][td]

5​

[/td][td]AX[/td][td]

9/9/2014​

[/td][td]INV4142[/td][/tr]

[tr][td]

6​

[/td][td]DR[/td][td]

1/9/2015​

[/td][td]INV4143[/td][/tr]

[tr][td]

7​

[/td][td]DR[/td][td]

3/25/2015​

[/td][td]INV4149[/td][/tr]

[tr][td]

8​

[/td][td]DR[/td][td]

4/11/2015​

[/td][td]INV4145[/td][/tr]

[tr][td]

9​

[/td][td]CRE[/td][td]

9/11/2014​

[/td][td]INV4150[/td][/tr]

[tr][td]

10​

[/td][td]CRE[/td][td]

4/11/2015​

[/td][td]INV4151[/td][/tr]

[tr][td]

11​

[/td][td]CRE[/td][td]

5/12/2015​

[/td][td]INV4154[/td][/tr]
[/table]

Sheet1 (processing)...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

1​

[/td][td]SKU[/td][td]Cut off date[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

2​

[/td][td]AX[/td][td]

10/30/2014​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

3​

[/td][td]AX[/td][td]

11/20/2014​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]AX[/td][td]

12/17/2014​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]AX[/td][td]

12/22/2014​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]AX[/td][td]

2/19/2015​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]AX[/td][td]

4/23/2015​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]AX[/td][td]

5/1/2015​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]AX[/td][td]

5/13/2015​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]AX[/td][td]

12/30/2014​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

11​

[/td][td]CRE[/td][td]

3/21/2015​

[/td][td]INV4151[/td][td]INV4154[/td][td][/td][td][/td][/tr]

[tr][td]

12​

[/td][td]CRE[/td][td]

4/1/2015​

[/td][td]INV4151[/td][td]INV4154[/td][td][/td][td][/td][/tr]

[tr][td]

13​

[/td][td]DR[/td][td]

9/11/2014​

[/td][td]INV4143[/td][td]INV4149[/td][td]INV4145[/td][td][/td][/tr]

[tr][td]

14​

[/td][td]DR[/td][td]

3/11/2015​

[/td][td]INV4149[/td][td]INV4145[/td][td][/td][td][/td][/tr]

[tr][td]

15​

[/td][td]DR[/td][td]

5/7/2015​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

16​

[/td][td]DR[/td][td]

12/4/2014​

[/td][td]INV4143[/td][td]INV4149[/td][td]INV4145[/td][td][/td][/tr]

[tr][td]

17​

[/td][td]DR[/td][td]

1/29/2015​

[/td][td]INV4149[/td][td]INV4145[/td][td][/td][td][/td][/tr]

[tr][td]

18​

[/td][td]DR[/td][td]

3/20/2015​

[/td][td]INV4149[/td][td]INV4145[/td][td][/td][td][/td][/tr]
[/table]

C2, control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX(Sheet2!$C$2:$C$11,SMALL(IF(Sheet2!$A$2:$A$11=$A2,
   IF(Sheet2!$B$2:$B$11>$B2,ROW(Sheet2!$C$2:$C$11)-ROW(Sheet2!$C$2)+1)),
   COLUMNS($C2:C2))),"")

 

[kikiarain]

Thanks so much!!

 

[Aladin Akyurek]

Thanks so much!!

You are welcome.

 

[kikiarain]

Can you please suggest the quickest way to tweak this formula to return blank if the Invoice is already listed in the rows above? For ex: cell C12 and D12 would be blank since these invoices are listed in row 11. Also, what does the below do in this formula?:

=IFERROR(INDEX(Sheet2!$C$2:$C$11,SMALL(IF(Sheet2!$A$2:$A$11=$A2,
IF(Sheet2!$B$2:$B$11>$B2,ROW(Sheet2!$C$2:$C$11)-ROW(Sheet2!$C$2)+1)),
COLUMNS($C2:C2))),"")
Thanks in advance!

 

[Aladin Akyurek]

Can you please suggest the quickest way to tweak this formula to return blank if the Invoice is already listed in the rows above? For ex: cell C12 and D12 would be blank since these invoices are listed in row 11. Also, what does the below do in this formula?:

=IFERROR(INDEX(Sheet2!$C$2:$C$11,SMALL(IF(Sheet2!$A$2:$A$11=$A2,
IF(Sheet2!$B$2:$B$11>$B2,ROW(Sheet2!$C$2:$C$11)-ROW(Sheet2!$C$2)+1)),
COLUMNS($C2:C2))),"")
Thanks in advance!

1. ROW(Sheet2!$C$2:$C$11)-ROW(Sheet2!$C$2)+1 produces an integer vector running fron 1 to N where N is equal to the number cells the range in question counts.

2. The way the output is arranged and the overlapping cut off dates makes processing such next to impossible.