Returning the Maximum Value

  • Thread starter Thread starter Bren
  • Start date Start date
B

Bren

If I have a list of names with associated figures, column
A is names and column B is numbers. I can get the MAX
formula to give me the highest number in B but how do I
get a formula to return the highest name in A related to
the answer in B. I tried IF formulas but it only takes up
to 8 IF references and my list is much larger than that.
It seems like a simple problem but I can't find the
formula.... I also can't use sort becuase if the numbers
change I will have to resort and that is no good.

HELP!
 
Hi Bren,

Try using INDEX and MATCH. Here's the example I used:

A B
1 John 1
2 Joe 4
3 Dave 2
4 Carl 9
5 Mike 3
6
7 Carl 9

Formula in Cell A7 is:
=INDEX(A1:A5,MATCH(B7,B1:B5,0))
Formula in Cell B7 is:
=MAX(B1:B5)

Hope that helps.

Regards,
James S
 
try - where max finds the row, match finds which row number and offset looks
one to the left.

=OFFSET(F1,MATCH(MAX(F1:F5),F1:F5)-1,-1)
 
Back
Top