Lookup query
 

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?

Lookup query
 

Try this:

=INDEX(A1:D1,MATCH(MAX(A5:D5),A5:D5,0))

--
Biff
Microsoft Excel MVP


"StephenAccountant" wrote in
message ...
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?

Lookup query
 

=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?

Lookup query
 

=SUMIF(A5:D5,MAX(A5:D5),A1:D1)

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

--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
=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?