refer indirectly to Name

[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?

[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)

--
Kevin Vaughn


"Hershmab" wrote:

> 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?

[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.

"Kevin Vaughn" wrote:

> 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)
>
> --
> Kevin Vaughn
>
>
> "Hershmab" wrote:
>
> > 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?

[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?



--
Kevin Vaughn


"Hershmab" wrote:

> 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.
>
> "Kevin Vaughn" wrote:
>
> > 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)
> >
> > --
> > Kevin Vaughn
> >
> >
> > "Hershmab" wrote:
> >
> > > 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?

[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.

"Kevin Vaughn" wrote:

> 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?
>
>
>
> --
> Kevin Vaughn
>
>
> "Hershmab" wrote:
>
> > 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.
> >
> > "Kevin Vaughn" wrote:
> >
> > > 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)
> > >
> > > --
> > > Kevin Vaughn
> > >
> > >
> > > "Hershmab" wrote:
> > >
> > > > 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?

[Kevin Vaughn]

You are welcome. Glad it worked for you.
--
Kevin Vaughn


"Hershmab" wrote:

> 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.
>
> "Kevin Vaughn" wrote:
>
> > 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?
> >
> >
> >
> > --
> > Kevin Vaughn
> >
> >
> > "Hershmab" wrote:
> >
> > > 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.
> > >
> > > "Kevin Vaughn" wrote:
> > >
> > > > 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)
> > > >
> > > > --
> > > > Kevin Vaughn
> > > >
> > > >
> > > > "Hershmab" wrote:
> > > >
> > > > > 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?