Excel 2007

I have data that looks like the following

A B C
Row 1 INTC MSFT IBM
Row 2 $1500 $2400 $1850

I need to write a formula that finds the maximum value in row 2, and
returns the corresponding value in row 1. The MAX() function will
return $2400, but I can't figure out how to get my formula to return
"MSFT" which is what I really need.

Any help will be greatly appreciated!

--Tom

Try
=LOOKUP(MAX(A2:C2),A2:C2,A1:C1)

"Thomas Mandeville" wrote:

Excel 2007

I have data that looks like the following

A B C
Row 1 INTC MSFT IBM
Row 2 $1500 $2400 $1850

I need to write a formula that finds the maximum value in row 2, and
returns the corresponding value in row 1. The MAX() function will
return $2400, but I can't figure out how to get my formula to return
"MSFT" which is what I really need.

Any help will be greatly appreciated!

--Tom

=LOOKUP(MAX(A2:C2),A2:C2,A1:C1)

The lookup_vector must be sorted in ascending order. You're getting the
correct result with this based on the OP's sample data by shear luck. Try
making A2 the max value then see what happens.

Try this:

=INDEX(A1:C1,MATCH(MAX(A2:C2),A2:C2,0))

If there is more than one instance of MAX the formula will match the
leftmost instance.


--
Biff
Microsoft Excel MVP


"Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote in
message ...
Try
=LOOKUP(MAX(A2:C2),A2:C2,A1:C1)

"Thomas Mandeville" wrote:

Excel 2007

I have data that looks like the following

A B C
Row 1 INTC MSFT IBM
Row 2 $1500 $2400 $1850

I need to write a formula that finds the maximum value in row 2, and
returns the corresponding value in row 1. The MAX() function will
return $2400, but I can't figure out how to get my formula to return
"MSFT" which is what I really need.

Any help will be greatly appreciated!

--Tom

=LOOKUP(MAX(A2:C2),A2:C2,A1:C1)

The lookup_vector must be sorted in ascending order. You're getting the
correct result with this based on the OP's sample data by shear luck. Try
making A2 the max value then see what happens.

I came upon the LOOKUP solution myself after posting the message last night.
It worked great on my little 5 item test list, but not so well on my 223
item real data. I was scratching my head over that. Now I know why it
didn't work for the real data. Thanks.

Try this:

=INDEX(A1:C1,MATCH(MAX(A2:C2),A2:C2,0))

If there is more than one instance of MAX the formula will match the
leftmost instance.

Works great. It is likely that there WILL be more than one instance of the
max value in the range, but I can deal with that using a text disclaimer or
something. We're just looking for the max value in the range for our own
internal purposes, as opposed to a report that would go to external
customers, so we don't need to pull out all the max values--we just need to
understand that there could be multiple occurrences of the max value.

Thanks for your help.

--Tom

=INDEX(A1:C1,MATCH(MAX(A2:C2),A2:C2,0))

If there is more than one instance of MAX the formula will match the
leftmost instance.

Works great. It is likely that there WILL be more than one instance of
the max value in the range, but I can deal with that using a text
disclaimer or something.

I ended up adding another formula that uses an IF statement to display a
text message if the max value occurs in the range more than once, and
displays nothing otherwise. I formatted that cell in red so that it grabs
attention when the message appears.

--Tom

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Thomas M." wrote in message
...
=LOOKUP(MAX(A2:C2),A2:C2,A1:C1)

The lookup_vector must be sorted in ascending order. You're getting the
correct result with this based on the OP's sample data by shear luck. Try
making A2 the max value then see what happens.

I came upon the LOOKUP solution myself after posting the message last
night. It worked great on my little 5 item test list, but not so well on
my 223 item real data. I was scratching my head over that. Now I know
why it didn't work for the real data. Thanks.

Try this:

=INDEX(A1:C1,MATCH(MAX(A2:C2),A2:C2,0))

If there is more than one instance of MAX the formula will match the
leftmost instance.

Works great. It is likely that there WILL be more than one instance of
the max value in the range, but I can deal with that using a text
disclaimer or something. We're just looking for the max value in the
range for our own internal purposes, as opposed to a report that would go
to external customers, so we don't need to pull out all the max values--we
just need to understand that there could be multiple occurrences of the
max value.

Thanks for your help.

--Tom

Try
=INDIRECT("R1C"&MATCH(MAX(A2:Z2),A2:Z2,0),FALSE)

Change Z to the last column in your range...
This assumes that your data is in row 1 and 2...

Sorry for giving you the wrong solution yesterday... I forgot about the
sorting requirement for LOOKUP...

Thanks to Biff for his inputs.

"Thomas M." wrote:

=INDEX(A1:C1,MATCH(MAX(A2:C2),A2:C2,0))

If there is more than one instance of MAX the formula will match the
leftmost instance.

Works great. It is likely that there WILL be more than one instance of
the max value in the range, but I can deal with that using a text
disclaimer or something.

I ended up adding another formula that uses an IF statement to display a
text message if the max value occurs in the range more than once, and
displays nothing otherwise. I formatted that cell in red so that it grabs
attention when the message appears.

--Tom

No problem.

--Tom

"Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote in
message ...
Try
=INDIRECT("R1C"&MATCH(MAX(A2:Z2),A2:Z2,0),FALSE)

Change Z to the last column in your range...
This assumes that your data is in row 1 and 2...

Sorry for giving you the wrong solution yesterday... I forgot about the
sorting requirement for LOOKUP...

Thanks to Biff for his inputs.

"Thomas M." wrote:

=INDEX(A1:C1,MATCH(MAX(A2:C2),A2:C2,0))

If there is more than one instance of MAX the formula will match the
leftmost instance.

Works great. It is likely that there WILL be more than one instance of
the max value in the range, but I can deal with that using a text
disclaimer or something.

I ended up adding another formula that uses an IF statement to display a
text message if the max value occurs in the range more than once, and
displays nothing otherwise. I formatted that cell in red so that it
grabs
attention when the message appears.

--Tom

No problem?
Does that mean it solved your problem? :-)
or it did not work? :-(

"Thomas M." wrote:

No problem.

--Tom

"Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote in
message ...
Try
=INDIRECT("R1C"&MATCH(MAX(A2:Z2),A2:Z2,0),FALSE)

Change Z to the last column in your range...
This assumes that your data is in row 1 and 2...

Sorry for giving you the wrong solution yesterday... I forgot about the
sorting requirement for LOOKUP...

Thanks to Biff for his inputs.

"Thomas M." wrote:

=INDEX(A1:C1,MATCH(MAX(A2:C2),A2:C2,0))

If there is more than one instance of MAX the formula will match the
leftmost instance.

Works great. It is likely that there WILL be more than one instance of
the max value in the range, but I can deal with that using a text
disclaimer or something.

I ended up adding another formula that uses an IF statement to display a
text message if the max value occurs in the range more than once, and
displays nothing otherwise. I formatted that cell in red so that it
grabs
attention when the message appears.

--Tom