Variable references in Lookup

  • Thread starter Thread starter C Dunhill
  • Start date Start date
C

C Dunhill

Hi there,

Is there any way to have a variable reference in Lookup?

I have a datasheet that may change over time (every couple
of months) but I don't want to constantly change all the
references in every cell.

Is it possible to have something like:

=lookup(A1,D1:End Cell,E1:End Cell)

- where "End Cell" is a reference to a cell, say G1, that
contains the location of the end cell (so I can change it
from, say D5 and E5 to D7 and E7 without having to change
every instance of Lookup).

Hope that makes sense and is it possible?

Cheers!
 
Wouldn't it be better to define a range name for the growing range?
insert>name>define>type in a name such as myrange>in the refers to box type
in
=offset($d$1,0,0,counta($d:$d),4)
the counta counts the cell in col D and the ,4 says to go 4 columns to the
right.
Try it, you'll love it.
 
If there is no other data below your data in Cols D&E, then the following should do it:-

=VLOOKUP(A1,OFFSET($D$1,,,COUNTA(D:D),2),2,0)

The OFFSET starts at cell D1 and then counts how many values there are in Col D. If there are 5
then it uses this 5 to determine the height of the range it is looking at, starting from D1
inclusive and working down. The first 2 in the equation represents the width of the range, so
this equates to the range D1:E5. The use of VLOOKUP and the optional fourth argument of 0 means
that your data does not have to be sorted in order to work.

As you add any data to Cols D&E, the COUNTA funtion will pick this up, increment the resultant
value from that function, and this increased value will be passed on to the OFFSET function, which
will in turn automatically create a larger range for the VLOOKUP function to work with.
 
Back
Top