VLOOKUP TO GET RESULT AS FORMULA

[aravindhan_3]

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]

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

[Bob Phillips]

I think he means

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

[Paul_Hossler]

@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

Instead of guessing, I think I need more information

Capture.JPG

[snb]

I fear the values in column B will be replaced by words in the OP's next reaction.

[aravindhan_3]

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