How, or can you, reference a cell value in a vlookup?

[Ron]

Simple example:
VLOOKUP(A1,B5:C30,2,FALSE)

B5 and/or C30 change evrytime I add data. So I am looking to do something
like:

VLOOKUP(A1,{"A2"}:C30,2,FALSE)

Where Cell A2 has the cell reference B5 in it. Or, I could use it to change
just the row number from a 5 to a 7. Like:

VLOOKUP(A1,B{"A2"}:C30,2,FALSE)

Any ideas?

Thanks,

 

[Bob Greenblatt]

Simple example:
VLOOKUP(A1,B5:C30,2,FALSE)

B5 and/or C30 change evrytime I add data. So I am looking to do something
like:

VLOOKUP(A1,{"A2"}:C30,2,FALSE)

Where Cell A2 has the cell reference B5 in it. Or, I could use it to change
just the row number from a 5 to a 7. Like:

VLOOKUP(A1,B{"A2"}:C30,2,FALSE)

Any ideas?

Thanks,

One way to do this is to put the starting cell address as text in one cell,
and the number of rows in another. For Example, suppose A2 contained "B5",
and A3 contained the number of rows, 26 in this case. Your formula would
then look like:
Vlookup(a1,offset(indirect(a2),0,0,b5,2),2,false)

 

[Ron]

Thanks Bob.

I will be playing with this now, to adapt it. I have quite a few
spreadsheets I can use info like this.

Appreciate the response.