ID| Name | Description| Price
--------- ------------ ------------------
1| Pen | Blue ink | 10
1| Pencil | HB | 20
2| Chair | 4 legs | 50
1| Paper | A4 | 25
This is a table of stationary that I am trying to use VLOOKUP to find the name of the highest price. I'm not sure but I am getting an error when I use
=VLOOKUP(MAX(D3:D6),A3:C6,2,FALSE)
but then I get an error
A value is not available to the formula and function
I think there is something wrong with the syntax of my VLOOKUP statement but I can't figure it out. Any help would be much appreciated
CodePudding user response:
The index value ie the column where you want to find the lookup value (your max value) needs to be the leftmost column. vlookup only works to the right.
So, index() with match() like so:
index(B3:B6,match(max(d3:d6),d3:d6,0))
Assuming all the columns etc as you don't show them, but you can take it from there.