Reverse Vlookup

Wuddle

New Member
Joined
Oct 29, 2002
Messages
22
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.
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Wuddle

New Member
Joined
Oct 29, 2002
Messages
22
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
Joined
Sep 6, 2002
Messages
9,123
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,158
Messages
5,600,054
Members
414,357
Latest member
Gemma_R

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top