Lookup Value in Table

[Thomas Mandeville]

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

 

[Sheeloo]

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

 

[T. Valko]

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

 

[Thomas M.]

=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

 

[Thomas M.]

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

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

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Sheeloo]

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.]

No problem.

--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.

 

[Sheeloo]

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