I am trying to creat a vlookup formula that instead of using range for its
table array will reference a cell which contains a range.

It will therefore look something like this:-

=VLOOKUP(A5,B3,FALSE)

where the cell B3 contains something like: 'a1:a100' but it is not working,
can you suggest a soloution?

Try this:

=VLOOKUP(A5,INDIRECT(B3),FALSE)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"nir020" wrote:

I am trying to creat a vlookup formula that instead of using range for its
table array will reference a cell which contains a range.

It will therefore look something like this:-

=VLOOKUP(A5,B3,FALSE)

where the cell B3 contains something like: 'a1:a100' but it is not working,
can you suggest a soloution?

=VLOOKUP(A5,INDIRECT(B3),2,FALSE)

for example

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"nir020" wrote in message
...
I am trying to creat a vlookup formula that instead of using range for its
table array will reference a cell which contains a range.

It will therefore look something like this:-

=VLOOKUP(A5,B3,FALSE)

where the cell B3 contains something like: 'a1:a100' but it is not
working,
can you suggest a soloution?

Actually, there is a flaw in your formula, in addition to your issue:

This:
=VLOOKUP(A5,B3,FALSE)

should be something like this:
=VLOOKUP(A5,INDIRECT(B3),(table_col_ref),FALSE)

example:
Since range a1:a100 is only one column:
=VLOOKUP(A5,INDIRECT(B3),1,FALSE)

Are you only trying to determine if the value in A5 is in the list of values
in A1:A100, which of course it would be. Or will the "reference" in B3 refer
to a multi-column range?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Ron Coderre" wrote:

Try this:

=VLOOKUP(A5,INDIRECT(B3),FALSE)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"nir020" wrote:

I am trying to creat a vlookup formula that instead of using range for its
table array will reference a cell which contains a range.

It will therefore look something like this:-

=VLOOKUP(A5,B3,FALSE)

where the cell B3 contains something like: 'a1:a100' but it is not working,
can you suggest a soloution?