Look UP formula for 2 and 3 conditions

**Excel Dumbo** · 04-02-2013, 05:21 AM

Hello,

Could you please look in to the attached file and advise formula for the two cases shown ?
I will need a formula to look up data matching multiple condtions.

Regards,
Dumbo

**Fotis1991** · 04-02-2013, 05:33 AM

In B5 and copy down and acroos for your first query.

=SUMPRODUCT(('LookUP data'!$B$5:$B$8=Result!$A5)*('LookUP data'!$A$5:$A$8=Result!$B$3)*('LookUP data'!$C$3:$E$3=Result!B$4)*('LookUP data'!$C$5:$E$8))

For you second, ament your months titles in row 12(example in C12, type FEBRUARY, not FEB). Then use in C13 and copy down and across, this one.

=SUMPRODUCT(('LookUP data'!$A$15:$A$17=Result!$A13)*('LookUP data'!$B$14:$M$14=Result!C$12)*('LookUP data'!$B$15:$M$17))

**Excel Dumbo** · 04-02-2013, 06:42 AM

Thanks Fotis.

Can i use this same formula when I need to add on information to the source data as the data keeps growing and I think the excel will slow down . Please suggest

**Fotis1991** · 04-02-2013, 06:53 AM

You are welcome.In fact, SUMPRODUCT is an ARRAY formula. So maybe using too many data will be slow...But as you use Excel 2003, i don't think that you have another choice, except maybe of using helper columns and SUMIF....