software

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-Proquot;nir020quot; wrote:

gt; I am trying to creat a vlookup formula that instead of using range for its
gt; table array will reference a cell which contains a range.
gt;
gt; It will therefore look something like this:-
gt;
gt; =VLOOKUP(A5,B3,FALSE)
gt;
gt; where the cell B3 contains something like: 'a1:a100' but it is not working,
gt; can you suggest a soloution?

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

for example

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

quot;nir020quot; gt; wrote in message
...
gt; I am trying to creat a vlookup formula that instead of using range for its
gt; table array will reference a cell which contains a range.
gt;
gt; It will therefore look something like this:-
gt;
gt; =VLOOKUP(A5,B3,FALSE)
gt;
gt; where the cell B3 contains something like: 'a1:a100' but it is not
working,
gt; 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 quot;referencequot; in B3 refer
to a multi-column range?

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

XL2002, WinXP-Proquot;Ron Coderrequot; wrote:

gt; Try this:
gt;
gt; =VLOOKUP(A5,INDIRECT(B3),FALSE)
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;nir020quot; wrote:
gt;
gt; gt; I am trying to creat a vlookup formula that instead of using range for its
gt; gt; table array will reference a cell which contains a range.
gt; gt;
gt; gt; It will therefore look something like this:-
gt; gt;
gt; gt; =VLOOKUP(A5,B3,FALSE)
gt; gt;
gt; gt; where the cell B3 contains something like: 'a1:a100' but it is not working,
gt; gt; can you suggest a soloution?