Excel formula help please!

HumanShield · 10 Apr 2007 at 14:15

I have 2 columns of data, a persons name in column A and a number in column B

I would like a formula that finds the highest number in column B, and returns the corresponding name. Is it possible?

Thanks!

ic1male · 10 Apr 2007 at 14:22

Could you not just do a quick sort by the numerical column in ascending order? Then the highest value will be the one at the bottom of the list.

|Ric| · 10 Apr 2007 at 14:23

Yes, if you swap the columns around

Then it is:
=VLOOKUP(MAX(A1:A5),A1:B5,2,FALSE)

Where B5 has the name of the last person in the column

HumanShield · 10 Apr 2007 at 14:25

sorted, formula i used was:

=IF(SUM(B6:B18)=0,"N/A",INDEX(A6:A18,MATCH(MAX(B6:B18),B6:B18,0)))

if anyone is interested!

|Ric| · 10 Apr 2007 at 14:26

Unfortunately VLOOKUP looks for the value in the first column so I don't think you can get away without swapping the columns. Maybe if you used column C to store a copy of the names and then hid it. Then you would have what I assume is a more logical layout