Help??

[danielle]

I have a spreadsheet with three different tables in it. All the tables are laid out the same as below but one is for 1yr, one is for 2 yrs and one is for 3yrs.

20 25 30 35
11 125 130 145 150
10 126 131 146 151
9 127 132 147 152

The heading across the top is the discount and the one down is the cost. In the second sheet I have used drop down boxes and option buttons and then used a vlookup for all the other formulas I needed. I want to be able to select 30% discount and 10 cost equals 146. Please can anyone help? Thanks

[Domenic]

First, define a name for each of your tables. For example, name your 1-year table Table1, your 2-year table Table2, and your 3-year table Table3...

Insert > Name > Define

Name: enter the table name, for example Table1

Refers to: enter/select the range of cells housing your table

Click Ok, and repeat for the other two tables

Then try the following formula...

=VLOOKUP(A1,CHOOSE(C1,Table1,Table2,Table3),MATCH(B1,{0,20,25,30,35},0),0)

...where A1 contains your drop-down menu for cost, B1 contains your drop-down menu for discount, and C1 is linked to your option buttons.

Hope this helps!

I have a spreadsheet with three different tables in it. All the tables are laid out the same as below but one is for 1yr, one is for 2 yrs and one is for 3yrs.

20 25 30 35
11 125 130 145 150
10 126 131 146 151
9 127 132 147 152

The heading across the top is the discount and the one down is the cost. In the second sheet I have used drop down boxes and option buttons and then used a vlookup for all the other formulas I needed. I want to be able to select 30% discount and 10 cost equals 146. Please can anyone help? Thanks

[danielle]

Unfortunately this hasnt worked. It has bought up a figures but not the one I wanted. Any ideas?

[Domenic]

Unfortunately this hasnt worked. It has bought up a figures but not the one I wanted. Any ideas?

I'm not sure off hand what the problem might be. If you'd like, you can email me a sample of your file and I'll try to help. You can email me at domenic22@sympatico.ca

Cheers!