VLOOKUP - Need Reverse?

  • Thread starter Thread starter Terry
  • Start date Start date
T

Terry

The VLookup function searches for value in the left-most
column of table_array and returns the value in the same
row based on the index_number.

Question is>>>

How do I lookup & return a value contained in a cell
further to the left of the table_array?


I have a Core Spreadsheet where I use VLOOKUP to return
Data from another sheet. Problem is, someone designed the
spreadsheet with the Primary Lookup reference key in the
middle. I have no problem getting data to the right of
the primary reference column..but cannot for the life of
me get data from the left.

I have an example if required..any help is much
appreciated...

TerryH
 
One way would be to use an OFFSET(.. MATCH(..) ..)

Maybe you could describe your example set-up
in plain text (do *not* post any attachment)
 
Terry

You can use a combinaiton of match + offset, ie... you
have your key on G:G and you want the value on B:B, you
are looking for the value on A1 so try:

=OFFSET(B1,MATCH(A1,G:G,0)-1,)

there are other workarrownd, I've been there and the best
solution I've found is to rework the source data so that
the key column is in A:A, that way My formulae is
simpler...

Hope this helps

Cheers
Juan
 
A common approach to return data from any position in relation to the lookup
column or row is the combination of INDEX() and MATCH().

The index column contains the data that you want returned, while the match
column contains the values that you are looking to match up.
The value to match is the first argument in the Match function.

With data to be returned listed in column A, and values to match up in
column D, and value to lookup in E1, here's an example:

=INDEX(A1:A100,MATCH(E1,D1:D100,0))

Since you're familiar with Vlookup, this Index aqnd Match combination can
also be utilized in a very similar fashion, using specific column numbers
within the formula to return data.

Taking your scenario:

Data list is A1 to E100
Your key column is right in the middle, column C.
The value to find is entered into F1:

=INDEX(A1:E100,MATCH(F1,C1:C100,0),1)
The final 1 on the formula returns from column A
Change it to anything up to a 5 (only 5 columns were indexed), to return
data from any of the columns, *including* the column you're using as the
key, column C.

This version on Index and Match is almost exactly the same as Vlookup,
without the restriction of the placement of the key column.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

The VLookup function searches for value in the left-most
column of table_array and returns the value in the same
row based on the index_number.

Question is>>>

How do I lookup & return a value contained in a cell
further to the left of the table_array?


I have a Core Spreadsheet where I use VLOOKUP to return
Data from another sheet. Problem is, someone designed the
spreadsheet with the Primary Lookup reference key in the
middle. I have no problem getting data to the right of
the primary reference column..but cannot for the life of
me get data from the left.

I have an example if required..any help is much
appreciated...

TerryH
 
Have you tried vlookup using a negative index number, it seems to me that i had done that before and it workd, the negative number was the count to the left of your search column
 
<<"Have you tried vlookup using a negative index number">>

Have you ?
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Have you tried vlookup using a negative index number, it seems to me that i
had done that before and it workd, the negative number was the count to the
left of your search column
 
Have you ?
Click to expand...

Just to grind this into the ground,

=VLOOKUP(1,{1,2,3},1)

returns 1, but

=VLOOKUP(1,{1,2,3},-1)

returns #VALUE!. In case one believes this is only so for arrays, enter

1 a
2 b
3 c
4 d

in A1:B4, then

=VLOOKUP("a",B1:B4,1)

returns "a", but

=VLOOKUP("a",B1:B4,-1)

again returns #VALUE!.
 
Many thanks for all your replies. I will include a small
snippet of the problem I am trying to resolve, and
hopefully I may be able to put together a resolution form
your expertise..

There are 2 worksheets involved in this. I will call them
Book1 & Book2

Book1 contains the VLOOKUP formula
Book2 has the reference data I need.

Both books contain what I will call the PK, albeit in
different columns. At the moment, moving the PK to Column
A is not an option for me ":-("

Book1. Sample of Columns. I don't need to go back before
Column R to input any formula

Column R contains the PK in Book1

R =PK
S
T
U
V
W
K
Y
Z = =VLOOKUP(R2,'Z:\Schedules\[Book2.xls]Sheet1'!
$AK:$AP,6,0)

The above VLOOKUP returns the data from Column AP, which
is great..however,I need to return data from
Columns/Cells before AK.

I am quite happy to send extracts form the sheets if you
think this may help.

many thanks


TerryH - (e-mail address removed)

ps..hope this makes sense
 
Using OFFSET(.. MATCH(..) ..) structured along
similar lines as your VLOOKUP could look something like ..:
(with col AK as the look-up col inside MATCH and
with $AK$1 as the anchor ref cell in OFFSET)

To extract from 1 col to the left of col AK, i.e. from col AJ:
=OFFSET($AK$1,MATCH(R2,'Z:\Schedules\[Book2.xls]Sheet1'!$AK:$AK,0)-1,-1)

To extract from 2 cols to the left of col AK, i.e. from col AI:
=OFFSET($AK$1,MATCH(R2,'Z:\Schedules\[Book2.xls]Sheet1'!$AK:$AK,0)-1,-2)

Just adjust/change the cols parameter in OFFSET
i.e. the rightmost number "-1", "-2" in the formula above,
to return the cols to the left of col AK

A "zero" as the cols param will return values in col AK itself,
while positive values "1","2" etc will return cols to the right
of col AK, viz. cols AL, AM, etc
 
Back
Top