Vlookup and MAX not working.

[gedkins]

Hi,

I am working with a pivot table and attempting to extract the highest value (count) found and returning a user name based on that. I am getting the dreaded N/A as a result of my formula. I am running Excel 2010. Here is the test case I have.

A	B
Bob	1
Fred	2
Bill	3
Sam	4

<tbody>
</tbody>

in a cell elsewhere on the sheet I have the following formula: =VLOOKUP(MAX(B2:B5),A2:B5,1,0). I get N/A. Any idea how to solve this problem? I would expect Sam as the value.

 

[AlanY]

try this instead

=INDEX(A2:A5,MATCH(MAX(B2:B5),B2:B5,0))

if you really need to use vlookup(), swap columns A & B like this

Excel 2012
	A	B
1	A	B
2	1	Bob
3	2	Fred
4	3	Bill
5	4	Sam
6
7	Sam
Sheet5

Cell Formulas
Range	Formula
A7	=VLOOKUP(MAX(A2:A5),A2:B5,2,0)

 

[Marcelo Branco]

INDEX/MATCH is the proper solution, but, just for fun, to use VLOOKUP with the columns in the wrong order, try:
=VLOOKUP(MAX(B2:B5),CHOOSE({1,2},B2:B5,A2:A5),2,0)

M.

 

[gedkins]

Sweet! It did the trick. Thx.