Look up values with multiple conditions

[Yulechka]

Hi! I need to write an excel formula that pulls numbers from the data tab based on three conditions: date received, account number and relevant month. Maybe it is simple formula, but I am starring at this report too long and feel stuck. Thank you for your help! Sample report is attached.

 

[NARAYANK991]

Hi ,

Does this formula work ?

=INDEX($H$5:$J$7, MATCH($A5, $G$5:$G$7,0), MATCH(B$4, $H$4:$J$4,0))

Enter this in B5 , and copy across and down.

Narayan

 

[Yulechka]

Thank you. It works great, but it missing one condition, if I need to aggregate data received on 4/1/17, then combine by account and months. I should include the date received on top on Pull section....

 

[NARAYANK991]

Thank you. It works great, but it missing one condition, if I need to aggregate data received on 4/1/17, then combine by account and months. I should include the date received on top on Pull section....

Hi ,

Please upload a workbook which contains a lot more data ; from the little data that is available in your earlier uploaded workbook , I am not able to understand your requirement.

Narayan

 

[Yulechka]

I need to sum all numbers on SUM tab by team, by month and by date received (three criterias). I set the file that I need to calculate all totals from April to December by team and date received. I hope it make sense. I tried Index and Match, but it gives me only one number, not a sum by month, by team and date received. Thanks.

 

[NARAYANK991]

Hi ,

Try this formula :

=SUMPRODUCT((Data!$C$3:$C$13 = $B4) * (Data!$D$2:$L$2 = C$3) * (Data!$A$3:$A$13 = $A$1) * (Data!$D$3:$L$13))

Narayan

 

[Yulechka]

Thank you so much Narayan!! It worked after a few attempts. My file has much more data and I didn't work in the beginning as my format was not number or something was off.... Thanks again!