# Use of Imbedded formulas including VLOOKUP, INDEX and MATCH to determine fuel cost.

1. ## Use of Imbedded formulas including VLOOKUP, INDEX and MATCH to determine fuel cost.

I am lost as last year's Easter Egg on this. I am trying to use nested formulas to calculate the fuel cost for a vehicle and am getting nowhere fast. Truly spinning my wheels. I am attaching the problem here and any help would be appreciated. I have put two tables to the right of the primary table to help me, but they are not really pertinent to the issue. I hope that I have put this in the right section as I am a new user. If I erred, let me know where I should put it and I will do better in the future.

2. ## Re: Use of Imbedded formulas including VLOOKUP, INDEX and MATCH to determine fuel cost.

See if this gets you on the right track:

In L11: =INDEX(Mileage!B:E,MATCH(\$H\$11&"*",Mileage!A:A,0)+2+VLOOKUP(B11,Models!B4:E12,4,0),MATCH(D11,Mileage!B3:E3,0))

Result is 37

you need the values in D11:D14 on Estimate form to be numeric (I placed a 4 in D11 to make it work)
I haven't done the Absolute Referencing (being called to dinner now - might not be back for a while)

Let us know how you make out with it.

3. ## Re: Use of Imbedded formulas including VLOOKUP, INDEX and MATCH to determine fuel cost.

Thanks, Cutter,

I see where that formula yields the 37 mph. Unfortunately I need to have it figure it on the mixed driving part of the mileage table (that answer should be 33) and the formula should be able to drag down the rows. I have tried to modify the numbers, but have not had any success. Will keep trying. I have learned a lot doing these exercises, but these imbedded functions are killing me.

Hal

4. ## Re: Use of Imbedded formulas including VLOOKUP, INDEX and MATCH to determine fuel cost.

Sorry Hal, I'm back now and no longer rushing to get this to you so corrections made to the formula.

OK, try this in L11 and drag down:

=INDEX(Mileage!B:E,MATCH(\$H\$6&"*",Mileage!A:A,0)+2+K11,MATCH(D11,Mileage!\$B\$3:\$E\$3,0))

Notice that the formula is partly based on the value in cell H6 - I suggest you put Data Validation - List in that cell using Highway,Mixed,City for the list.
Changing that cell will direct the formula to the appropriate table on the Mileage sheet.

5. ## Re: Use of Imbedded formulas including VLOOKUP, INDEX and MATCH to determine fuel cost.

I tried the formula you wrote and included a data validation rule for cell H6, but I am still getting an error message of "Too many arguments...", so nothing happens. This is frustrating, but I know it can be done, it's just my learning curve right now appears to be a wall. I do appreciate the help. Thanks and I will keep trying. Hal

6. ## Re: Use of Imbedded formulas including VLOOKUP, INDEX and MATCH to determine fuel cost.

Copy the formula that's giving the error and paste it here.

7. ## Re: Use of Imbedded formulas including VLOOKUP, INDEX and MATCH to determine fuel cost.

Here it is.

=INDEX(Mileage!B:E,MATCH(\$H\$6&"*",Mileage!A:A,0+2+K11,MATCH(D11,Mileage!\$B\$3:\$E\$3,0))

8. ## Re: Use of Imbedded formulas including VLOOKUP, INDEX and MATCH to determine fuel cost.

You're missing a bracket.

Yours: =INDEX(Mileage!B:E,MATCH(\$H\$6&"*",Mileage!A:A,0+2+K11,MATCH(D11,Mileage!\$B\$3:\$E\$3,0))

Mine: =INDEX(Mileage!B:E,MATCH(\$H\$6&"*",Mileage!A:A,0)+2+K11,MATCH(D11,Mileage!\$B\$3:\$E\$3,0))

9. ## Re: Use of Imbedded formulas including VLOOKUP, INDEX and MATCH to determine fuel cost.

Thanks. I call myself checking this carefully. It reads what I type, not what I meant.

10. ## Re: Use of Imbedded formulas including VLOOKUP, INDEX and MATCH to determine fuel cost.

Always best to copy provided formulas and paste them directly to your formula bar.

Hope you've got it working now. I'm shutting down for the night.

11. ## Re: Use of Imbedded formulas including VLOOKUP, INDEX and MATCH to determine fuel cost.

Thanks Cutter, that was very helpful. I am working on more problems. Keeping this open for now, but will mark it solved when I complete the whole worksheet. Hal

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1