Hlookup/max value?

[swjtx]

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

[Biff]

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

"swjtx" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> swjtx
> ------------------------------------------------------------------------
> swjtx's Profile:
> http://www.excelforum.com/member.php...o&userid=29716
> View this thread: http://www.excelforum.com/showthread...hreadid=494418
>

[Biff]

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

"Biff" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "swjtx" <[email protected]> wrote in
> message news:[email protected]...
>>
>> 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
>>
>>
>> --
>> swjtx
>> ------------------------------------------------------------------------
>> swjtx's Profile:
>> http://www.excelforum.com/member.php...o&userid=29716
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=494418
>>
>
>

[swjtx]

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

[Neil M]

Biff to the rescue, again!!!

Neil M