CODEWITHSUNDEEP
excelindexinglookup
user3288358
user3288358

Reputation: 15

Excel index formula

I need help creating a formula that will figure out the correct mpg of a car based on its weight class, engine and type of driving.

Cars

On this worksheet the engine type and weight class are listed (D4:D12 and F4:F12)

After figuring out which type of driving is done (Highway, Mixed, City) the value that corresponds to the correct weight and engine type. For example, on the mixed chart, a car with a weight class of 1 and engine type of 4, the mpg is 33.

I just need a formula that can automate the process if the car model changes.

MPG

Upvotes: 0

Views: 119

Answers (1)

Ken
Ken

Reputation: 1166

I would structure the three mpg tables into a single table with four columns:

Driving Type / Weight Class / Engine Size / MPG

Then create a pivot table based on that table. For ease put Weight and Engine class on the Rows, and Driving Type on the column, and then put the MPG entry as the data.

You can then use the GetPivotData function to query the PivotTable based on the three values, and it would return the appropriate MPG entry.

Alternatively, you could create the single table with a column with "Driving Type & Weight Class & Engine Size" in one column, and then have the MPG entry in the second column. A vlookup on the table with the three fields catenated together would return the MPG entry.

Upvotes: 1

Related Questions