Dynamic range in formula

  • Thread starter Thread starter Mr. Smith
  • Start date Start date
M

Mr. Smith

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.)
 
Thank you both!
My example had some flaws to it regarding references, but anyway you helped
me out.

Mr. Smith
 
Back
Top