Dynamic range in formula

Hi.
Is it possible to read a named range that is written into a cell and base a
formula on it? I'll try to explain.

In cell B2 the value "data_rng1" is inserted.
In cell C2 the value "Mike" is inserted
In cell D2 the formula =VLOOKUP(C2;B2;1;FALSE) is inserted

Is there anyway that the formula in D2 would manage to understand that the
value in B2 is a range that it should use to lookup the value in C2?
God damn it would be a nice one.

Dearest
Mr. Smith

One way

=VLOOKUP(C2;INDIRECT(B2);1,FALSE)

--

Regards,

Peo Sjoblom

"Mr. Smith" wrote in message
...
Hi.
Is it possible to read a named range that is written into a cell and base
a
formula on it? I'll try to explain.

In cell B2 the value "data_rng1" is inserted.
In cell C2 the value "Mike" is inserted
In cell D2 the formula =VLOOKUP(C2;B2;1;FALSE) is inserted

Is there anyway that the formula in D2 would manage to understand that the
value in B2 is a range that it should use to lookup the value in C2?
God damn it would be a nice one.

Dearest
Mr. Smith

Try:
=vlookup(c2;indirect(b2);1;false)

But it looks as though you're trying to return the same value as you're looking
for (column = 1).

If you're looking for a match to see if that value is on the list:

=if(isnumber(match(c2;indirect(b2);0));"Yep";"nope ")
(if data_rng1 is a single column range)
or
if data_rng1 is multicolumn:
=IF(ISNUMBER(MATCH(C2;INDEX(INDIRECT(B2);;1),0));" yep";"nope")

(which looks longer than =vlookup! I'd use that, too.)







"Mr. Smith" wrote:

Hi.
Is it possible to read a named range that is written into a cell and base a
formula on it? I'll try to explain.

In cell B2 the value "data_rng1" is inserted.
In cell C2 the value "Mike" is inserted
In cell D2 the formula =VLOOKUP(C2;B2;1;FALSE) is inserted

Is there anyway that the formula in D2 would manage to understand that the
value in B2 is a range that it should use to lookup the value in C2?
God damn it would be a nice one.

Dearest
Mr. Smith

--

Dave Peterson

Thank you both!
My example had some flaws to it regarding references, but anyway you helped
me out.

Mr. Smith


"Dave Peterson" wrote in message
...
Try:
=vlookup(c2;indirect(b2);1;false)

But it looks as though you're trying to return the same value as you're
looking
for (column = 1).

If you're looking for a match to see if that value is on the list:

=if(isnumber(match(c2;indirect(b2);0));"Yep";"nope ")
(if data_rng1 is a single column range)
or
if data_rng1 is multicolumn:
=IF(ISNUMBER(MATCH(C2;INDEX(INDIRECT(B2);;1),0));" yep";"nope")

(which looks longer than =vlookup! I'd use that, too.)







"Mr. Smith" wrote:

Hi.
Is it possible to read a named range that is written into a cell and
base a
formula on it? I'll try to explain.

In cell B2 the value "data_rng1" is inserted.
In cell C2 the value "Mike" is inserted
In cell D2 the formula =VLOOKUP(C2;B2;1;FALSE) is inserted

Is there anyway that the formula in D2 would manage to understand that
the
value in B2 is a range that it should use to lookup the value in C2?
God damn it would be a nice one.

Dearest
Mr. Smith

--

Dave Peterson