• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup (or other solution) to this problem requested

S

supersonic373

New Member
Hi All - First time to post so thanks in advance for the help in responses.


I have a data set as below where customers receive certain % rebate from a

product. I am putting together a sheet for someone to be able to use a pull down

in a cell to select the product and the customer, then automatically using vlookup

or other solution, to locate the correct % rebate based on what is selected on these

two pull down selections (Customer Name & Product#). Based on the % rebate then

other calculations are performed before providing the user with a result.


Customer Name Product1 Product2 Product3

ABC x% y% x%

DEF f% x% e%

EFG e% g% y%


Thanks again for any responses/solutions. Ron
 
Hi, supersonic373!


Try this, assuming your upper table is ranged from A1:D4.


In A6, add data validation from list in A2:A4.


In B6, type: =SI(A6="";"";COINCIDIR(A6;A2:A4;0)) -----> in english: =IF(A6="","",MATCH(A6,A2:A4,0))


In A7, add data validation from list in B1:D1.


In B7, type: =SI(A7="";"";COINCIDIR(A7;B1:D1;0)) -----> in english: =IF(A7="","",MATCH(A7,B1:D1,0))


In A8 type: =SI(Y(B6<>"";B7<>"");INDICE(B2:D4;B6;B7);"") -----> in english: =IF(AND(B6<>"",B7<>""),INDEX(B2:D4,B6,B7),"")


Regards!
 
Hi SirBJ7,


Thanks so much for the reply.


In inputting the =SI commands I am getting an error which appears to be related to the

first set of "" after the = (e.g. =SI(A6="" .... ). Excel gives back a non-descript error and is highlighting the "".


Any thoughts? Not sure if I can upload a screenshot to explain if it would help. Ron


UPDATE: I just changed the formulas to the IF statements in your instructions and seems to work. Sorry, did I misunderstand something? Is there an "SI" function in Excel?


Again, Thanks.
 
Hi, supersonic373!


I use a spanish version of Excel, so I "write" the formulas in my language and when you open the workbook you "read" them in you language. Also the dots, commas and semicolons change according to regional configuration.


To avoid mistypings, I copy/paste my formulas and translate them asides so as you can use them in english. So, no, there isn't a SI function in Excel, it's just my IF.


Regards!
 
Hi SirJB7,


Interesting, had not even thought to consider SI being Spanish (yes, even with the "in English" prompt). Thanks again for the help, really do appreciate your response to solve this problem.


Cheers!
 
You must log in or register to reply here.
Back
Top