vlookup with Name Ranges

Hello, little help please


In the vlookup formula I know you can use a name range as below:


=VLOOKUP(A3,named_rage,1,FALSE)

but is it possible to refer to the “named_range” through a cell

E.g.

=VLOOKUP(A3,A3,1,FALSE)


Can you use the contents of cell A3 to refer to the named_range????

And if so how???


Note there are many “named_ranges” on multiple sheets



Thanking you in advance

I just wrote a quick procedure to evaluate the formulas you give it. This would work with way more than just vlookup too.

In a module;

Function EvaluateFormula(sString As String) As String

EvaluateFormula = Evaluate(sString)

End Function

In a cell;

=EvaluateFormula("VLOOKUP(3," & C1 & ",2)")
This expression evaluated to "D" (check the table below)

The value of C1 = "LookupRange"

LookupRange is a named range that points to a group of data elsewhere on the sheet. Like this:
0 A
1 B
2 C
3 D
4 E
5 F
6 G
7 H
8 I
9 J

Cheers

I am trying to work with formulas rather than modules


INDIRECT worked perfectly


Many Thanks

well, I guess I learned something too... thanks!