Lookup query

[StephenAccountant]

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 A55 and return the corresponding
value in the same column in cells A1 to D1.

In this case the result would be 2006.

Any ideas?

 

[T. Valko]

Try this:

=INDEX(A11,MATCH(MAX(A55),A55,0))

 

[Teethless mama]

=SUMPRODUCT(--(A55=MAX(A55)),A11)

 

[T. Valko]

=SUMIF(A55,MAX(A55),A11)

However, if there are duplicate max values then this and SUMPRODUCT will
return the incorrect result.