Reverse Vlookup

[Wuddle]

Is it possible to create a vlookup that works in reverse. For example, in a simple 2 column table using Text in the 1st Col and No's in the 2nd, I want to find the highest number from Col 2 and lookup the adjacent cell in Col 1.

 

[Juan Pablo González]

Try an INDEX/MATCH combination:

=INDEX(A1:A10,MATCH(MAX(B1:B10),B1:B10,0))

 

[Wuddle]

THats excellent thanks very much. Is there any chance you could explain the Index syntax to me cos I'm not understanding it at all?

 

[DonkeyOte]

On 2002-11-01 11:09, Juan Pablo G. wrote:
Try an INDEX/MATCH combination:

=INDEX(A1:A10,MATCH(MAX(B1:B10),B1:B10,0))

The Index sets a range of data (A1:A10) and you tell it which row of that data you want to return eg

a1 = 1
a2 = 2
a3 = 3

=INDEX(A1:A3,2) will return 2
=INDEX(A1:A3,3) will return 3

Juan Pablo is using this in turn with MATCH (to determine the row to return from your index range) - he is finding the max value in the range, then matching it in the range to return the row number of the match

using the a1=1 etc example

=MAX(A1:A3) will return 3
=MATCH(MAX(A1:A3),A1:A3) will also return 3 as the value 3 (the max) is in row 3.

so

=INDEX(B1:B10,MATCH(MAX(A1:A3),A1:A3,0))

will read

=INDEX(B1:B10,3)

so will return the value in B3.

Make sense?

 

[Wuddle]

Many thanks, again