=SUMPRODUCT(--(A5:D5=MAX(A5:D5)),A1:D1)


"StephenAccountant" wrote:

Hi,

I need to lookup some data, find the largest amount, and then return a value
in a different cell.

Eg:

A1 B1 C1 D1
2007 2006 2005 2004

A5 B5 C5 D5
20 35 19 4


So I have to look up the largest value in A5:D5 and return the corresponding
value in the same column in cells A1 to D1.

In this case the result would be 2006.

Any ideas?