insert formula via INDEX or VLOOKUP

**Angel160** · 08-02-2007, 09:11 AM

Hello,

I have a spreadsheet which requires a number of a differing commssion rates. I have been able to do this so far using the IF function, but have now reached the limit to the number of arguments I can enter.

Is it possible to insert a formula into a sreadsheet using either the VLOOKUP or INDEX functions? If not, can anyone think of an alternative?

Thank you in advance

**daddylonglegs** · 08-02-2007, 09:28 AM

You probably need to give a few more details, but you can often use LOOKUP or VLOOKUP in this situation. Do you want to pay a single commission rate for the whole amount, dependant on the exact amount, or do you need to calculate different parts of that figure at different percentages?

If it's the former then here's an example.

Sales amount in A1

You want to calculate 10% of A1 if A1 < 100, 15% if A1 is between 101 and 200, 20% if 200 or above.

=LOOKUP(A1,{0,100,200},{0.1,0.15,0.2})

you can extend this for any number of bands

**Angel160** · 08-02-2007, 09:54 AM

Hi,

This is the formula I've got at the moment (the J cells refer to an INDEX formula)

=IF(OR(J2=5,J2=16,J2=24,J2=26,J2=10,J2=40),IF(U2<100000,IF(U2*0.0035<30,30,U2*0.0035),((U2-100000)*0.0025)+350),IF(OR(J2=1,J2=2,J2=3,J2=4,J2=6,J2=7,J2=9,,J2=11,J2=12,J2=14,J2=15,J2=13,J2=17,J2=18,J2=19,J2=20,J2=22,J2=26,J2=28,J2=29,J2=30,J2=31,J2=32,J2=33,J2=34,J2=35,J2=38,J2=39,J2=41),IF(U2*0.0035<30,30,U2*0.0035),IF(OR(J2=18,J2=23),IF(U2*0.003<30,30,U2*0.003),IF(OR(J2=8,J2=27),IF(U2*0.0035<25,25,IF(AND(U2<500000,U2>100000),((U2-100000)*0.0025)+350,IF(U2>=500000,U2*0.0025,U2*0.0035)))))))

I have 4 different commssion rates used for 41 different clients. I now have a 5th rate I need to use, but cannot add anymore arguments to the above formula.

I was hoping there was a way of using either INDEX or VLOOKUP to insert just a portion of the above formula dependant on the client.

**daddylonglegs** · 08-02-2007, 10:26 AM

That's quite a formula

I'd probably try to simplify things a little with a table. If you put the possible values for J2 in Y1:Y41 and the specific commission type (1-5) for each of these in Z1:Z41 then you could use this formula

=CHOOSE(VLOOKUP(J2,Y1:Z41,2,0),MAX(U2*0.25%+MIN(U2,100000)*0.1%,30),MAX(U2*0.35%,30),MAX(U2*0.3%,30), MAX(25,U2*0.25%+MIN(100000,U2)*0.1%)-(U2>500000)*100,formula5)

replace formula 5 with your new commission calculation.

Note: I've simplified the first four formulas from your originals