LookUP formula matching multiple conditions

[Ecel Dumbo]

Hello,


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


Thanks

http://speedy.sh/UbEGV/Rebates-Query.xls

 

[DaveTurton]

Something like this?


=INDEX('Look UP'!$E$2:$E$14,MATCH(1,(Result!A2='Look UP'!$A$2:$A$13)*(Result!B2='Look UP'!$C$2:$C$13)*(Result!C2='Look UP'!$D$2:$D$13),0))


insert with Ctrl + Shift + Enter

 

[NARAYANK991]

Hi ,


Try this in D2 , and copy downwards :


=IFERROR(LOOKUP(A2&B2&TEXT(C2,"00"),'Look UP'!$A$2:$A$13&'Look UP'!$C$2:$C$13&TEXT('Look UP'!$D$2:$D$13,"00"),'Look UP'!$E$2:$E$13),0)


Narayan

 

[Ecel Dumbo]

THanks Narayan and Dave Turton for your valubale time


Narayan , I applied your formula but I am getting #NAME error

Dave Turton, I am getting correct percentage discount for exact match of units ordered.

The concept is if we order 12 or more units of certain product, we get 30% discount.

With your formula, if units ordered is 12, we get 30%, but if units ordered is 13, % is coming up a zero. If we order 36 or more, we get 40%


please look at attached sheet


http://speedy.sh/HjgQK/Rebates-Query-version-2.xls

 

[SirJB7]

Hi, Ecel Dumbo!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/LookUP%20formula%20matching%20multiple%20conditions%20-%20Rebates%20Query%20version%202%20%28for%20Ecel%20Dumbo%20at%20chandoo.org%29.xlsx


You've to do a few things:


a) order data in worksheet "Look UP" by Company, Product Number and Units sold, all ascending


b) place an entry with Units Sold = 0 and Discount = 0% for each combination of Company & Product number, as the MATCH function with the "Less than" parameter (1), needs a base value


c) in worksheets Result place this array formula in D2 and copy down thru D11:

=INDICE('Look UP'!A$2:E$16;COINCIDIR(A2&"_"&B2&"_"&TEXTO(SUMAR.SI.CONJUNTO(C$2:C2;A$2:A2;A2;B$2:B2;B2);"000000000");'Look UP'!A$2:A$16&"_"&'Look UP'!C$2:C$16&"_"&TEXTO('Look UP'!D$2:D$16;"000000000");1);5) -----> in english: =INDEX('Look UP'!A$2:E$16,MATCH(A2&"_"&B2&"_"&TEXT(SUMIFS(C$2:C2,A$2:A2,A2,B$2:B2,B2),"000000000"),'Look UP'!A$2:A$16&"_"&'Look UP'!C$2:C$16&"_"&TEXT('Look UP'!D$2:D$16,"000000000"),1),5)

Remember that array formulas should be entered with Ctrl-Shift-Enter instead of just Enter.


d) check your own previous results, as many of them differ from those of calculated by formula, but many of them now equals where before they didn't.


If I don't understand wrongly the discount per Company & Product Number is given for the accumulated units from first row thru actual row inclusive. If that's right, then check your values, otherwise elaborate a bit more and post again the updated workbook.


Regards!

 

[NARAYANK991]

Hi ,


First , my mistake :


I did not mention it , but SirJB7 has ; your lookup table in the 'Look UP' tab needs to be sorted on Column A , Column C and Column D.


Second , the #NAME? error :


You have completed 93 posts , and I think in addition to getting your job done , mastering Excel should also be an objective ; I am sure the reason for this error has been mentioned in relation to one of your earlier posts ; I suggest you try to find this out ; the solution would also have been given.


Narayan

 

[Ecel Dumbo]

THanks SirJB .


THanks Narayan for your advise.

 

[SirJB7]

Hi, Ecel Dumbo!

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!