LookUP formula matching muitiple conditions

**Excel Dumbo** · 05-08-2013, 03:56 AM

Hello,

Please refer attached work sheet and suggest a formula to look up discount %s matching multiple values.

Thanks

**Fotis1991** · 05-08-2013, 04:01 AM

=SUMPRODUCT(('Look UP'!$A$2:$A$30=A2)*('Look UP'!$C$2:$C$30=B2)*('Look UP'!$D$2:$D$30=C2)*('Look UP'!$E$2:$E$30)) ??

**AZ-XL** · 05-08-2013, 11:07 AM

hi

Nice question. Hope my solution will hel you.

d2 cell =IFERROR(VLOOKUP(C2,IF((A2='Look UP'!$A$2:$A$13)*(Result!B2='Look UP'!$C$2:$C$13),'Look UP'!$D$2:$E$13,""),2,TRUE),0) Ctrl+Shift+Enter

If you use excel 2003 version then use above formula
=IF(ISERROR(VLOOKUP(C2,IF((A2='Look UP'!$A$2:$A$13)*(Result!B2='Look UP'!$C$2:$C$13),'Look UP'!$D$2:$E$13,""),2,TRUE)),0,VLOOKUP(C2,IF((A2='Look UP'!$A$2:$A$13)*(Result!B2='Look UP'!$C$2:$C$13),'Look UP'!$D$2:$E$13,""),2,TRUE))

Units sold column in lookup tabe should be sorted from smalles to largest in order to get the right percentage

Uploaded file.

**Excel Dumbo** · 05-08-2013, 08:21 PM

THanks Fotis and AZ excel for your valuable time.

Hi Fotis, that formula does work if there is an exact match, but please see my attached spreadsheet for the result i require

Hi AZ, I opened your attacment and saw that i t was working, but when i did that on my file, it did not work. please see attached spreadsheet. also please explain the logic of sorting from smallest to largest