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.
 

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.

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?
 

Forum statistics

Threads
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.
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