vlookup and offset

  • Thread starter Thread starter RachelS
  • Start date Start date
R

RachelS

Hi,

Can anyone PLEASE help me with the following? - it's driving me cRaZy!!


I want to look up a value in column B and return the contents of the
cell across 4 and down 4 from the value looked up in column B. Is this
possible?

Rachel
 
RachelS said:
Hi,

Can anyone PLEASE help me with the following? - it's driving me cRaZy!!


I want to look up a value in column B and return the contents of the
cell across 4 and down 4 from the value looked up in column B. Is this
possible?

Rachel
Click to expand...

Two possibilities:

=INDEX($F$5:$F$14,MATCH(A1,$B$1:$B$10,0))
In this equation, the row offset of 4 is done by making the range in column
F start 4 rows below that in column B.

=INDEX($F:$F,MATCH(A1,$B:$B,0)+4)
In this equation, the row offset of 4 is added to the position found by the
MATCH.
 
Hi

When data in column B are in range B2:B100, and the value you are looking up
is stored in cell X2, then:
=OFFSET($B$1,MATCH($X$2,$B$2:$B100,0)+4,4)

PS. VLOOKUP returns the value, not the reference to it, or the position of
this value- so it's no help for you in this case.


Arvi Laanemets
 
So this cries for the question, what is the difference in using Index() and
Offset()?
TIA,,


Arvi Laanemets said:
Hi

When data in column B are in range B2:B100, and the value you are looking up
is stored in cell X2, then:
=OFFSET($B$1,MATCH($X$2,$B$2:$B100,0)+4,4)

PS. VLOOKUP returns the value, not the reference to it, or the position of
this value- so it's no help for you in this case.


Arvi Laanemets
Click to expand...
 
Have you looked up "Index" and "Offset" in Help?

JMay said:
So this cries for the question, what is the difference in using Index() and
Offset()?
TIA,,


Arvi Laanemets said:
Hi

When data in column B are in range B2:B100, and the value you are
Click to expand...
looking
Click to expand...
 
Both say the function will return a "reference"; even here I'm a bit
confused as to me it looks like the they are retuning "values in the
referenced cell".
 
You can't display a reference so the behavior is to have the returned
reference diplay the value of that reference.
 
Yes, I quite understand this confusion - I was confused for a long time
about this. To expand a little on Tom Ogilvy's reply, a function is used as
PART of a formula. For a function (such as INDEX or OFFSET) that returns a
reference, it returns it to the formula, not to the cell. The behaviour is
just as it would be if you replaced the function by that reference.

For example, consider the case of a formula containing a function that
returns a reference to a single cell:
=OFFSET($A$1,3,0)
This is equivalent to
=A4
so you see what you would have done had you used the formula =A4 (that is,
you see the contents of A4).

It is easier to understand (at least, I found it so) in the case of a
function that returns a reference to a range of cells. So, for example, in
the formula
=SUM(OFFSET($A$1,3,0,4))
the OFFSET function returns a reference to the range A4:A7, so the formula
is equivalent to
=SUM(A4:A7)
which is easy to understand.
 
Thanks Paul - I appreciate the time you took to give me the various
examples, especially the last one - which "synched-it" for me; I now have a
better understanding of how things work;;;;;
JMay
 
Hi

I read your's with Paul conversation here, and I think I have to add
something.

For most cases, the difference is like the color of cat - it can be
anything, until she can catch the mouse. Usually everone uses this one, with
which he is more confident. I myself started to use OFFSET, and I use INDEX
only occasionally now.

When you look more closely to syntax of both, then with OFFSET you set the
anchor cell, and move the returned reference relatively to it - up or down,
to left or to right. The only limit is, that the returned reference must be
in limits of sheet. With INDEX, you set the range with all possible return
values, and the returned reference must be in this range.
Maybe the only difference occurs, when the reference to multi-cell range is
returned. With INDEX(), you can return the reference to specific row or
column in range (when I'm not completly wrong with my understanding of it's
syntax). With OFFSET, you can return the reference to any rectangle on
worksheet.


Arvi Laanemets


JMay said:
So this cries for the question, what is the difference in using Index() and
Offset()?
TIA,,


Arvi Laanemets said:
Hi

When data in column B are in range B2:B100, and the value you are
Click to expand...
looking
Click to expand...
 
Back
Top