Hlookup/max value?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

swjtx - 18 Dec 2005 06:01 GMT

Hi,

What I am trying to do is look for the max value in a row and return
the relevant column header

Example:

1-----2-----3-----4------5-----6-----7----

35---29----16----3----21----45----5----

So, the function should look down a row, find the max value of 45 and
return the column header of 6.

I have been tryinga combination of hlookup and max value but haven't
been able to product the desired results.

Any help would be greatly appreciated.

swjtx

Signature

swjtx

Reply to this Message

Biff - 18 Dec 2005 06:21 GMT

Hi!

Try this:

=INDEX(A1:A7,MATCH(MAX(B1:B7),B1:B7,0))

Where A1:A7 are the headers, B1:B7 ae the numeric values.

NB: if there are multiple instances of the max value the formula will return
the corresponding header of the first instance.

Biff

> Hi,
>
[quoted text clipped - 16 lines]
>
> swjtx

Reply to this Message

Biff - 18 Dec 2005 07:57 GMT

Ooops!

> =INDEX(A1:A7,MATCH(MAX(B1:B7),B1:B7,0))

Should be:

=INDEX(A1:G1,MATCH(MAX(A2:G2),A2:G2,0))

Where A1:G1 are the headers, A2:G2 ae the numeric values.

Biff

> Hi!
>
[quoted text clipped - 29 lines]
>>
>> swjtx

Reply to this Message

swjtx - 18 Dec 2005 16:48 GMT

Hi and Thanks! It worked like a charm.

I inserted the $ so I could drag the formula down without changing the
range (a tip from a different person on this site) like this:

=INDEX(A1:G1,MATCH(MAX($A$2:$G$2),$A$2:$G$2,0))

swjtx

Signature

swjtx

Reply to this Message

Neil M - 19 Dec 2005 00:45 GMT

Biff to the rescue, again!!! :)

Neil M

Reply to this Message