# Reverse Vlookup

#### Wuddle

##### New Member
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.

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### Juan Pablo González

##### MrExcel MVP
Try an INDEX/MATCH combination:

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

#### Wuddle

##### New Member
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

##### MrExcel MVP
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))

=INDEX(B1:B10,3)

so will return the value in B3.

Make sense?

#### Wuddle

##### New Member
Many thanks, again

Replies
1
Views
95
Replies
1
Views
35
Replies
8
Views
270
Replies
13
Views
93
Replies
13
Views
205

1,170,935
Messages
5,872,807
Members
432,948
Latest member
Yordi

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back