Lookup question

M

Monte Comeau

Can you use VLOOKUP to find the value of a cell that is to the left and not
in the same row?
 
L

Leo Heuser

Monte

It's not clear to me, what you mean by "not
in the same row", but to find the value of a
cell to the left (of the lookup column) you can
use a combination of INDEX and MATCH like
in this example:

=INDEX(Sheet1!$A$2:$A$100,MATCH(A2,Sheet1!$B$2:$B$100,0))

where B2:B100 is the lookup column in sheet1 and A2
may be in e.g. sheet2, where the formula resides as well.

Another option would be (for the same data)

=OFFSET(Sheet1!$B$2,MATCH(A2,Sheet1!$B$2:$B$100,0)-1,-1)

where the rightmost -1 designates the column one step to
the left of the lookup column, so the formula could be used
as a general lookup formula with positive numbers meaning
columns to the right and negative numbers columns to the left.
(Of course HLOOKUP() or VLOOKUP() is a better choice
for positive values.)
Be aware, that -1 etc. is an absolute value and if the column in
question is deleted from the sheet no updating of the formula
takes place.

If you have a *lot* of these formulae, the INDEX() version is
to be preferred, since OFFSET() is a volatile function, which
means, it is recalculated each time a recalculation occurs
in the sheet. Not so with INDEX(). It only recalculates, when
there is a change in its arguments.
 
L

Leo Heuser

Addendum.

If you mean e.g. find the value in the first
column to the left of the lookup column and
2 cells above the lookup cell, this is one way
of doing it:

=INDEX(Sheet1!$A$2:$A$100,MATCH(A2,Sheet1!$B$2:$B$100,0)-2)
 
M

Monte Comeau

I cannot make any of this worl :(

Basically what I want to do is return the text label from the cell 4 cells
to the left. I get a lot of NA errors...
 
M

Monte Comeau

Can someone tell me what is wrong with this?

=VLOOKUP(L27,C8:C66,-4)

I have a column G of numbers.
I have picked out the lowest from G and returned it to L27.
The coresonding text I want to be returned to J27 is located 4 columns to
the left in column C away from column G where the lookup value is.
My table array is C8:C66 where all the text labels are.
My column index number is -4 as it is 4 columns to the left.

I get the #NA error. I need help.


=VLOOKUP(L27,C8:C66,-4)
 
M

Monte Comeau

One thing that may be causing trouble...

My LookUpValue comes from cell L27 which was generated by the following

=TEAMS!Q26
 
M

Monte Comeau

And the TEAMS!Q26 data was generated with =SMALL(J10:J316,1)
Does any of this matter...
 
L

Leo Heuser

VLOOKUP cannot have a negative offset.
The function is used on a *table*, where
column 1 is the column containing the value from
L27 and the other columns (column 2, 3, 4 etc.)
are the columns from which you pick a value
from one.

INDEX cannot have a negative offset for
the same reason.

Two possible solutions to your problem:

=INDEX($C$8:$C$66,MATCH(L27,$G$8:$G$66,0))

or

=OFFSET($G$8,MATCH(L27,$G$8:$G$66,0)-1,-4)

Assuming that the numbers in column G are in
G8:G66.
 
F

Frank Kabel

Hi Monte
VLOOKUP requieres that your lookup value is in the leftmost column of
your matrxi. So a column index of -4 is not valid. For this kind of
lookups you have to use a combination of INDEX and MATCH. e.g.
=INDEX(C8:C66,MATCH(L27,G8:G66,0),1)

in your existing formula you search in column C8:C88 for the value in
L27 and would like to return the corresponding value of column C-4
columns (which does not exist).

HTH
Frank
 
F

Frank Kabel

Hi Monte
see post above. You have to check the syntax. the third parameter of
MATCH is used as comparison typ (-1, 0, 1)

Frank
 
Top