Does anyone know how I can do this?

I basically have 10 sheets of car information (around 4000 rows on each sheet)

I have used =SMALL(‘Sheet1:Sheet10’N2….. to find the smallest MPG out of all the sheets.

Now I need to find the name of that car with that value. I guess I will have to used and Index and Match function, but I have no clue how to do it.

Any help would be appreciated 🙂

Have you looked at VLOOKUP – if you have a known value, but want something from a different column in the data set, that’s the one to use.

Eg VLOOKUP(“Fred”,Array, 3) would return “Fiesta” from Column 3 of the table:

Fred, Ford, Fiesta
Mary, VW, Gold
John, Audi, TT

vlookup means your search value has to be in the first column of your array so that doesn’t help.

For example in mine the car names are in column B, while the MPG value is in column N.

I’d probably use Match and then Offset.

Be aware it will match the first value of the Minimum so if two cars have the same value you won’t see both and if one ford Ka is giving great mileage but 10 others are rubbish and there are 20 minis giving almost as good mileage (and better overall) you will miss them.

Some sort of pivot table might be better to find the useful info…

vlookup means your search value has to be in the first column of your array so that doesn’t help.

You can always re-arrange your arrays and save it as a Macro….

I generally do everything in VBA, as it’s much more flexible.

Create hidden columns which mirror columns B and N and run the VLOOKUP over those.

Does this really need to be a repeatable formula?

Would it not just be quicker to look through the sheets and write down the cars with the lowest MPG or sort the sheets by column n?