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.


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]








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.