Complex VLOOKUP

[danielle]

In sheet 1 I have 3 tables which looks similar to the below but larger. one for 1 year one for 2 year and one for three year.

1 2 3
Oneyear 0% 5% 10%
1 11.5 167 155.00 140.00
2 11 162 150.00 135.00
3 10 151 139.00 124.00
4 9 140 129.00 114.00

In Sheet two I am using combo boxes and option buttons to select options that th user requires. So if the user wants 10 cost (which is vertical) and 5% discount the figure I need to show is 139.00 on sheet 2.

I have the 1234 going vertical and 123 hortizonal because I thought I need to have it for the vlookup sum I had been using which is

=VLOOKUP(F5,CHOOSE(F11,Oneyear,Twoyear,Threeyear),MATCH( F9,{0,5,10,15,20,25,30},0),0)

F5 being 10 the cost F11 being if it is one, two, or three year table and F9 being 5%. But the formula keep bring up the wrong figure.

Can anyone help????

[Domenic]

Try the following...

=VLOOKUP(F5,CHOOSE(F11,Oneyear,Twoyear,Threeyear), MATCH( F9,{0,5,10,15,20,25,30},0)+1,0)

Hope this helps!

In sheet 1 I have 3 tables which looks similar to the below but larger. one for 1 year one for 2 year and one for three year.

1 2 3
Oneyear 0% 5% 10%
1 11.5 167 155.00 140.00
2 11 162 150.00 135.00
3 10 151 139.00 124.00
4 9 140 129.00 114.00

In Sheet two I am using combo boxes and option buttons to select options that th user requires. So if the user wants 10 cost (which is vertical) and 5% discount the figure I need to show is 139.00 on sheet 2.

I have the 1234 going vertical and 123 hortizonal because I thought I need to have it for the vlookup sum I had been using which is

=VLOOKUP(F5,CHOOSE(F11,Oneyear,Twoyear,Threeyear),MATCH( F9,{0,5,10,15,20,25,30},0),0)

F5 being 10 the cost F11 being if it is one, two, or three year table and F9 being 5%. But the formula keep bring up the wrong figure.

Can anyone help????

[danielle]

This work but when I select something different from the disocunt drop down boxes it comes up #N/A. If I change the cost or the years it works?

Any Ideas? would it be because it is horizontal?

[danielle]

Actually it doesnt consider the discount at all?

[Domenic]

If the value in F9 is 5% (.05), then the array constant should change as follows...

{0,.05,.10,.15,.20,.25,.30}

Does this help?

This work but when I select something different from the disocunt drop down boxes it comes up #N/A. If I change the cost or the years it works?

Any Ideas? would it be because it is horizontal?