help w indirect and index,match

[excelhelp_84]

I'm working on a user input worksheet where the formulas I create wil
calculate from user input in the highlighted blue fields and retur
correct output based on user entry. its attached in a 2010 exce
workbook

Anway the output formulas require the following (i'll skip the ca
make/descrp):

1) Estimated annual cost of gas based on selected type of driving,weigh
class,annual miles traveled, gas price and engine type

2) Est. insurance cost based on owner's region of residence, drivin
record, and residential status (hint: include an intermediate calc tha
lookups up the region number based on the state...blah blah.....hav
this part below:

VLOOKUP('Estimate Form'!$F$5,Statestbl,4,FALSE)

Lastly,
3) est. avaerage annual maintainence costs for 1st 3 years of operatio
based on the selected car model and annual miles driven (total 3 y
maintainence costs divide by 3 plus cost of yr of oil changes); to th
base maintainence cost, add $29 per oil change for each 5k miles driven

I've tried using vlookup and match alone but that doesn't work. Not sur
how to use index and indirect well.

Basically I'm looking for at least one formula that fulfills th
requirement and could help me out on either 1 2 or 3. If I can get tha
should be able to finish the rest.

We're supposed to use the index and indirect functions for these
However, if there's another way to solve it that works, I'd appreciat
seeing it.

Thank

+-------------------------------------------------------------------
|Filename: ckg%20operating%20costs_matt_hickman.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=646
+-------------------------------------------------------------------

 

[Don Guillett]

I'm working on a user input worksheet where the formulas I create will

calculate from user input in the highlighted blue fields and return

correct output based on user entry. its attached in a 2010 excel

workbook



Anway the output formulas require the following (i'll skip the car

make/descrp):



1) Estimated annual cost of gas based on selected type of driving,weight

class,annual miles traveled, gas price and engine type



2) Est. insurance cost based on owner's region of residence, driving

record, and residential status (hint: include an intermediate calc that

lookups up the region number based on the state...blah blah.....have

this part below:



VLOOKUP('Estimate Form'!$F$5,Statestbl,4,FALSE)



Lastly,

3) est. avaerage annual maintainence costs for 1st 3 years of operation

based on the selected car model and annual miles driven (total 3 yr

maintainence costs divide by 3 plus cost of yr of oil changes); to the

base maintainence cost, add $29 per oil change for each 5k miles driven



I've tried using vlookup and match alone but that doesn't work. Not sure

how to use index and indirect well.



Basically I'm looking for at least one formula that fulfills the

requirement and could help me out on either 1 2 or 3. If I can get that

should be able to finish the rest.



We're supposed to use the index and indirect functions for these.

However, if there's another way to solve it that works, I'd appreciate

seeing it.



Thanks





+-------------------------------------------------------------------+

|Filename: ckg%20operating%20costs_matt_hickman.zip |

|Download: http://www.excelbanter.com/attachment.php?attachmentid=646|

+-------------------------------------------------------------------+

And just what is this for
We're supposed to use the index and indirect functions for these

 

[Spencer101]

And just what is this for
We're supposed to use the index and indirect functions for these

Sounds like "Homework Help" to me..

 

[excelhelp_84]

Sounds like "Homework Help" to me...

I'm sorry but I'm sure there are lots of ppl that come on here fo
"homework help" they may be less subtle than me but still... What's th
deal? is that against the forum rules?

If I can get pointed in the right direction, that can help me as well
The format of the formula should be something along the lines of:

INDEX(INDIRECT(VLOOKUP("Disc",TablDef,MATCH("Tabl_Def",TablCol,0),FALSE),TRUE),MATCH($E$15,INDIRECT(VLOOKUP("Disc",TablDef,MATCH("row_num",TablCol,0),FALSE),TRUE),1),MATCH("Discount",INDIRECT(VLOOKUP("Disc",TablDef,MATCH("col_num",TablCol,0),FALSE),TRUE),0)