looking up maximum valus in a cell range

Hi

I two tables in excel. One table has 10 columns with the headings
Dates, HUK, HBUK, IMG, Bentley, MAN, BMW, HBE, HNL

The second table is below. The numbers in the second table below are the max of each of the object columns above

HUK 5
HBUK 7
IMG 2
BGBL 8
MAN 9
BMW 10
HBE 7
HNL 9

However i need to add a column to the second table which displays the dates listsed in the first table. However the dates need to be for the max numbers below. For example if the huk max is 5 then the date on the corresponding row is 12/04/2011 e.g.

Does anyone know how to do this?

Thanks

Jeskit

HI
I think vlookup can be used. Upload a sample file for more explanation and to experiment.

Regards

Hi
Thanks for your sample.

Try this
In a supportive column (D) write the column names corresponding to each heading in table 1.

e.g in D2 write "G" for HUK and in D3 write "E" for HBUK.

then use index, match with indirect function to make a formula that changes itself to right as it copied down.

Please Login or Register  to view this content.

copied down

Remember:

It works if only one maximum value is present.

I am not sure if it works across multiple sheets.

Change the number in indirect function if you added rows above the table..

Change the column name in supportive column if you added new column.

See attached also

Regards