Hello,
Please refer attached work sheet and suggest a formula to look up discount %s matching multiple values.
Thanks
Hello,
Please refer attached work sheet and suggest a formula to look up discount %s matching multiple values.
Thanks
=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)) ??
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
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.
Appreciate the help? CLICK *
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
Sorry AZ, it was my mistake. That formula works well now. Excellent . THanks heaps.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks