Reverse Vlookup

  • Thread starter Thread starter MAXWELL
  • Start date Start date
M

MAXWELL

In a vlookup formula, you can only look up from left to
right by identifying the number of columns to the right.
Is there a way or formula that will do the same thing
looking up a column to the left of the lookup value? I
tried the following and it di not work...
=VLOOKUP($A1,'[SHEET1.xls]XREF'!$A1:$F500,-3,0)

Look at the -3,0 part of it. It does not or cannot look
backwards.

Help
 
Use INDEX and MATCH, eg:-

=INDEX($A$5:$A$30,MATCH(A1,$E$5:$E$30,0))

This will look for the value from A1 in the range E5:E30, and then use the MATCH function to
return a value representing the number of rows it had to go down to find the MATCH. That value
that gets returned by the MATCH part of the formula is then passed to the INDEX function as the
argument to tell it how many rows to go down the data in Col A.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



MAXWELL said:
In a vlookup formula, you can only look up from left to
right by identifying the number of columns to the right.
Is there a way or formula that will do the same thing
looking up a column to the left of the lookup value? I
tried the following and it di not work...
=VLOOKUP($A1,'[SHEET1.xls]XREF'!$A1:$F500,-3,0)

Look at the -3,0 part of it. It does not or cannot look
backwards.

Help
Click to expand...
 
First of all, you use A1:F500 as the table dimension, how can you look up
anything to the left of column A? If you mean 2 columns to the left of F (F
being # 1) use

=INDEX(D1:D500,MATCH(A1,F1:F500,0))
 
Back
Top