PDA

View Full Version : [SOLVED] VLOOKUP TO GET RESULT AS FORMULA

aravindhan_3
03-28-2016, 09:17 AM
Hello everyone!

I have a formula where I have to use atleast 15 nested if functions to get my result, instead can we have a list of Criteria & formulas in a separate table, and bring back the formula based on my criteria. like vlookup, based on lookup value bring the formula from the table instead of values.

I have attached a sample file for reference

Regards
Arvind

snb
03-28-2016, 09:48 AM
Keep it simple

in D2:

=CHOOSE(CODE(B2)-64;10;7,5;5;2;1;-20;-15;-5)*C2/100

=CHOOSE(CODE(B2)-64,10,7.5,5,2,1,-20,-15,-5)*C2/100

xld
03-28-2016, 02:54 PM
I think he means

=CHOOSE(CODE(B2)-64,10,7.5,5,2,1,80,85,95)*C2/100

Paul_Hossler
03-28-2016, 04:41 PM
@snb & XLD --

I was still wondering about the OP's intent

1. The column is labeled 'Sales + Commission' but the formula 'appears' to only generate a 10%, 7.5%, 5%,2%, and 1% commission (no sales) for A - E types

2. Making an assumption for a G, the formula C5-(15%*C5) returns 425 'Sales + Commission' for 500 'Sales', which doesn't seem right

My guess was

=CHOOSE(CODE(B2)-64,1.1,1.075,1.05,1.02,1.01,0.8,0.85,0.95)*C2

15766

snb
03-28-2016, 11:23 PM
I fear the values in column B will be replaced by words in the OP's next reaction.

aravindhan_3
03-29-2016, 06:36 AM
Hi,

Thanks for all your replies, sorry for not being clear. the file that I gave was only sample, there can be any formulas there, can be a if function, can be a lookup, I am looking for a macro or idea to get any formulas based on lookup

Regards
Arvind