View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Thomas M. Thomas M. is offline
external usenet poster
 
Posts: 68
Default Lookup Value in Table

=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