Lookup question

  • Thread starter Thread starter Monte Comeau
  • Start date Start date
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?
 
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.
 
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)
 
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...
 
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)
 
One thing that may be causing trouble...

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

=TEAMS!Q26
 
And the TEAMS!Q26 data was generated with =SMALL(J10:J316,1)
Does any of this matter...
 
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.
 
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
 
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
 
Back
Top