Reverse Vlookup

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
 
K

Ken Wright

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
 
P

Peo Sjoblom

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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top