refer indirectly to Name

H

Hershmab

My application has a large number of related look-up tables.

Each table is defined as a Name. In each row of my main worksheet, I can use
data to calculate the Name of its relevant table as text.

But how can I look up the table?

In other words, is there a function like INDIRECT that will return a value
that VLOOKUP will treat as a Name for its first argument?
 
K

Kevin Vaughn

Are you talking about named ranges (sometimes referred to as Named Formulae?)
If so, when you are calculating the name of the table are you returning it
to a cell. If so again, then you can use Indirect like so. Say the named
range of the table is in column A and the lookup value is in column B, then a
formula like the following should work:

=VLOOKUP(B14, INDIRECT(A14), 2, FALSE)
 
H

Hershmab

I had already tried using INDIRECT like that, with A14 (in your example)
containing the Named range as text. Unfortunately it does not work - the
return value is #VALUE.

A related question is how to reference a cell in another worksheet, where
the worksheet name is variable.
 
K

Kevin Vaughn

To answer your 2nd question first:

=VLOOKUP(B6, INDIRECT("'" & A6 & "'!b14:c15"),2,FALSE)

As far as it not working, it should. My range names look like this:

Table1 =Sheet1!$C$6:$D$9
table2 =Sheet1!$F$13:$G$16
and it works fine for me. I would guess something else is causing the
#value error. What is your complete formula? and what do your named ranges
look like?
 
H

Hershmab

I tried the complete VLOOKUP formula - and it worked correctly! What I do not
understand is why the formula =INDIRECT(A14) returns #VALUE. But I will not
pursue this point as it is of no practical consequence.

Thanks for persuading me to try again.
 
Top