Need help with multiple criteria and multiple results

[SARABECK]

Hello,

I have been working on this formula for the past few days and it is just not working at all, please help?

In sheet 1 - in column A, I input dates from Sheet2 and Sheet3 and insert the desire formula in C2-C9 it should pull in the invoice# and the quantities in D2-C9 from sheet2 and sheet3.

Sheet1

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]Helper Column for Sheet 3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date[/TD]
[TD]Product[/TD]
[TD]Invoice[/TD]
[TD]QTY[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]04/26/18[/TD]
[TD]01234[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]04/27/18[/TD]
[TD]01234[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]04/30/18[/TD]
[TD]01234[/TD]
[TD]YYY123[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]04/30/18[/TD]
[TD]01234[/TD]
[TD]AC1234[/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]04/30/18[/TD]
[TD]01234[/TD]
[TD]WC5678[/TD]
[TD]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]05/01/18[/TD]
[TD]01234[/TD]
[TD]AE2222[/TD]
[TD]100[/TD]
[TD]3rd Party Purchase[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]05/03/18[/TD]
[TD]01234[/TD]
[TD]AG3333[/TD]
[TD]30[/TD]
[TD]Shipped[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]05/04/18[/TD]
[TD]01234[/TD]
[TD]BU5555[/TD]
[TD]50[/TD]
[TD]Received[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]05/04/18[/TD]
[TD]01234[/TD]
[TD]WE6666[/TD]
[TD]1000[/TD]
[TD]Received[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2 (Report) Only the last 3 business days of the monh

Criteria: Product and Date
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]G[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]U[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Month[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Product[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Product Description[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Invoice#[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]QTY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]04/30/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]01234[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Blah[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]TU1452[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]120[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]04/01/18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]04/30/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]01234[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Blah[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AB1111[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]154[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]04/15/18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]04/30/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]01234[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Blah[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]22222[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]04/26/18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]04/30/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]01234[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Blah[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]22222[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]-50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]04/26/18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]04/30/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]01234[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Blah[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]YYY123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]04/30/18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]04/30/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]01234[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Blah[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AB1234[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]04/30/18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]04/30/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]01234[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Blah[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]WC5678[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]04/30/18[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet 3 (Report 2) All info related to the May

Criteria: Product and Transaction Type
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]F[/TD]
[TD][/TD]
[TD][/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD][/TD]
[TD][/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Product[/TD]
[TD][/TD]
[TD][/TD]
[TD]Transaction Type[/TD]
[TD]Date[/TD]
[TD][/TD]
[TD][/TD]
[TD]Invoice#[/TD]
[TD][/TD]
[TD][/TD]
[TD]QTY[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]01234[/TD]
[TD][/TD]
[TD][/TD]
[TD]3rd Party Purchase[/TD]
[TD]05/01/18[/TD]
[TD][/TD]
[TD][/TD]
[TD]AE2222[/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]01234[/TD]
[TD][/TD]
[TD][/TD]
[TD]Shipped[/TD]
[TD]05/03/18[/TD]
[TD][/TD]
[TD][/TD]
[TD]CB4444[/TD]
[TD][/TD]
[TD][/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]01234[/TD]
[TD][/TD]
[TD][/TD]
[TD]Shipped[/TD]
[TD]05/03/18[/TD]
[TD][/TD]
[TD][/TD]
[TD]CB4444[/TD]
[TD][/TD]
[TD][/TD]
[TD]-20[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]01234[/TD]
[TD][/TD]
[TD][/TD]
[TD]Shipped[/TD]
[TD]05/03/18[/TD]
[TD][/TD]
[TD][/TD]
[TD]AG3333[/TD]
[TD][/TD]
[TD][/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]01234[/TD]
[TD][/TD]
[TD][/TD]
[TD]Received[/TD]
[TD]05/04/18[/TD]
[TD][/TD]
[TD][/TD]
[TD]BU5555[/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]01234[/TD]
[TD][/TD]
[TD][/TD]
[TD]Received[/TD]
[TD]05/04/18[/TD]
[TD][/TD]
[TD][/TD]
[TD]WE6666[/TD]
[TD][/TD]
[TD][/TD]
[TD]1000[/TD]
[/TR]
</tbody>[/TABLE]


Thank you.

 

[Saba Sabaratnam]

Hi SaraBeck,

The challenge is here that dates are repeated multiple times in Sheet2 and Sheet3. To address this, i have developed some formulas. However, it will pick invoices from the top. That is if you have 4/30/18 in Sheet2 multiple times and repeat the date in Sheet 1 only three times, it will pick up first three invoices as listed below. [TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"]TU1452[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]AB1111[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: right"]22222[/TD]
[/TR]
</tbody>[/TABLE]
.
Having said that, enter the following array formula in C2 in Sheet1 by pressing Control + Shift + Enter:


=IFERROR(IF(ISERROR(INDEX(Sheet2!$D$1:$D$100,SMALL(IF(A2=Sheet2!$A$1:$A$100,ROW($1:$100),""),COUNTIFS($A$2:A2,A2)))),INDEX(Sheet3!$D$1:$D$100,SMALL(IF(A2=Sheet3!$C$1:$C$100,ROW($1:$99),""),COUNTIFS($A$2:A2,A2))),INDEX(Sheet2!$D$1:$D$100,SMALL(IF(A2=Sheet2!$A$1:$A$100,ROW($1:$100),""),COUNTIFS($A$2:A2,A2)))),"")

Enter the following formula in D2

=IFERROR(IF(ISNA(INDEX(Sheet2!E:E,MATCH(Sheet1!C2,Sheet2!D:D,0))),INDEX(Sheet3!E:E,MATCH(Sheet1!C2,Sheet3!D:D,0)),INDEX(Sheet2!E:E,MATCH(Sheet1!C2,Sheet2!D:D,0))),"")

Enter the following formula in E2

=IFERROR(INDEX(Sheet3!B:B,MATCH(Sheet1!C2,Sheet3!D:D,0)),"")

Kind regards

Saba

 

[SARABECK]

Hi Saba,

Thank you so much Saba for your help.

From Sheet 2
The only info that is needed is the last 3 business dates.

Looking at April month it would be 4/26/18, 4/27/18, and 4/30/18.

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]3[/TD]
[TD]04/30/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]01234[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Blah[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]22222[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]04/26/18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]04/30/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]01234[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Blah[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]22222[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]-50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]04/26/18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]04/30/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]01234[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Blah[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]YYY123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]04/30/18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]04/30/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]01234[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Blah[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AB1234[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]04/30/18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]04/30/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]01234[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Blah[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]WC5678[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]04/30/18[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 475"]
<tbody>[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

The date is listed in column Y
4/26/18 - This is zeroed out (It was invoiced and looks like then cancelled)
4/30/18 - 3 invoices were issued

From Sheet 3

5/3/18 - 3 invoices were issued and invoice# CB4444 shouldn't have been picked up instead should have picked up invoice#AG3333.

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]2[/TD]
[TD][/TD]
[TD]01234[/TD]
[TD][/TD]
[TD][/TD]
[TD]Shipped[/TD]
[TD]05/03/18[/TD]
[TD][/TD]
[TD][/TD]
[TD]CB4444[/TD]
[TD][/TD]
[TD][/TD]
[TD] 20[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]01234[/TD]
[TD][/TD]
[TD][/TD]
[TD]Shipped[/TD]
[TD]05/03/18[/TD]
[TD][/TD]
[TD][/TD]
[TD]CB4444[/TD]
[TD][/TD]
[TD][/TD]
[TD]-20[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]01234[/TD]
[TD][/TD]
[TD][/TD]
[TD]Shipped[/TD]
[TD]05/03/18[/TD]
[TD][/TD]
[TD][/TD]
[TD]AG3333[/TD]
[TD][/TD]
[TD][/TD]
[TD]30[/TD]
[/TR]
</tbody>[/TABLE]


Your help is very appreciated.

Thank you again.

 

[SARABECK]

In the formula, the product has not be incorporated and it should be since i have over 50-80 products, for which, the last 3 business days and the month of May info will need to be pull from the 2 reports.


Thank you

 

[Saba Sabaratnam]

Hi SaraBeck,

Based on your requirements, I have included a helper column in Sheet3 and modified my formulas.

Helper Column F of Sheet3: I used a SUMIFs formula to total net invoice amount. If total quantity of an invoice amount is zero, it will be NOT be picked in Sheet1. Enter the following formula in cell F2 in Sheet3 and copy it down
=SUMIFS(E:E,D:D,D2)

Enter the following array formula in C2 in Sheet1 by pressing Shift + Control + Enter and copy it down
=IFERROR(IF(ISERROR(INDEX(Sheet2!$D$1:$D$1000,SMALL(IF((A2=Sheet2!$F$1:$F$1000)*(B2=Sheet2!$B$1:$B$1000),ROW($1:$1000),""),COUNTIFS($A$2:A2,A2)))),INDEX(Sheet3!$D$1:$D$1000,SMALL(IF((A2=Sheet3!$C$1:$C$1000)*(B2=Sheet3!$A$1:$A$1000)* (Sheet3!$F$1:$F$1000>0),ROW($1:$1000),""),COUNTIFS($A$2:A2,A2))),INDEX(Sheet2!$D$1:$D$1000,SMALL(IF((A2=Sheet2!$F$1:$F$1000)*(B2=Sheet2!$B$1:$B$1000),ROW($1:$1000),""),COUNTIFS($A$2:A2,A2)))),"")

Please note that the above formula includes your product as a criteria and it is based on the following assumptions:

• You enter dates in Column A of Sheet 1
• If it the first argument which looks for answer in Sheet2 produces an error, it looks for answer in Sheet3. If both arguments produce error, it puts nothing in cells

Formulas from Column D to E in Sheet remains the same as given in my reply post.

Kind regards

Saba

 

[SARABECK]

Thank you so much Saba. I'll try this and let you know.

Thank you again.

 

[SARABECK]

Hi Saba,

I tired your formula in my sample file and it didn't work.

Here is my:

https://expirebox.com/download/3c922bd930fd5142438812fcf2767bbf.html

Thank you for your help.

 

[Saba Sabaratnam]

Hi SaraBeck,

I tested my formula in your spreadsheet. it worked.

=IFERROR(IF(ISERROR(INDEX(Sheet2!$D$1:$D$1000,SMALL(IF((A2=Sheet2!$F$1:$F$1000)*(B2=Sheet2!$B$1:$B$1000),ROW($1:$1000),""),COUNTIFS($A$2:A2,A2)))),INDEX(Sheet3!$D$1:$D$1000,SMALL(IF((A2=Sheet3!$C$1:$C$1000)*(B2=Sheet3!$A$1:$A$1000)*(Sheet3!$F$1:$F$1000>0),ROW($1:$1000),""),COUNTIFS($A$2:A2,A2))),INDEX(Sheet2!$D$1:$D$1000,SMALL(IF((A2=Sheet2!$F$1:$F$1000)*(B2=Sheet2!$B$1:$B$1000),ROW($1:$1000),""),COUNTIFS($A$2:A2,A2)))),"")

Copy the formula as it is in C2 and enter as array formula by pressing Shift + Control + Enter.

The formula in your spreadsheet in C2 is not the same as the above formula.

Kind regards

Saba