software

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?

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 Vaughnquot;Hershmabquot; wrote:

gt; My application has a large number of related look-up tables.
gt;
gt; Each table is defined as a Name. In each row of my main worksheet, I can use
gt; data to calculate the Name of its relevant table as text.
gt;
gt; But how can I look up the table?
gt;
gt; In other words, is there a function like INDIRECT that will return a value
gt; that VLOOKUP will treat as a Name for its first argument?

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.

quot;Kevin Vaughnquot; wrote:

gt; Are you talking about named ranges (sometimes referred to as Named Formulae?)
gt; If so, when you are calculating the name of the table are you returning it
gt; to a cell. If so again, then you can use Indirect like so. Say the named
gt; range of the table is in column A and the lookup value is in column B, then a
gt; formula like the following should work:
gt;
gt; =VLOOKUP(B14, INDIRECT(A14), 2, FALSE)
gt;
gt; --
gt; Kevin Vaughn
gt;
gt;
gt; quot;Hershmabquot; wrote:
gt;
gt; gt; My application has a large number of related look-up tables.
gt; gt;
gt; gt; Each table is defined as a Name. In each row of my main worksheet, I can use
gt; gt; data to calculate the Name of its relevant table as text.
gt; gt;
gt; gt; But how can I look up the table?
gt; gt;
gt; gt; In other words, is there a function like INDIRECT that will return a value
gt; gt; that VLOOKUP will treat as a Name for its first argument?

To answer your 2nd question first:

=VLOOKUP(B6, INDIRECT(quot;'quot; amp; A6 amp; quot;'!b14:c15quot;),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 Vaughnquot;Hershmabquot; wrote:

gt; I had already tried using INDIRECT like that, with A14 (in your example)
gt; containing the Named range as text. Unfortunately it does not work - the
gt; return value is #VALUE.
gt;
gt; A related question is how to reference a cell in another worksheet, where
gt; the worksheet name is variable.
gt;
gt; quot;Kevin Vaughnquot; wrote:
gt;
gt; gt; Are you talking about named ranges (sometimes referred to as Named Formulae?)
gt; gt; If so, when you are calculating the name of the table are you returning it
gt; gt; to a cell. If so again, then you can use Indirect like so. Say the named
gt; gt; range of the table is in column A and the lookup value is in column B, then a
gt; gt; formula like the following should work:
gt; gt;
gt; gt; =VLOOKUP(B14, INDIRECT(A14), 2, FALSE)
gt; gt;
gt; gt; --
gt; gt; Kevin Vaughn
gt; gt;
gt; gt;
gt; gt; quot;Hershmabquot; wrote:
gt; gt;
gt; gt; gt; My application has a large number of related look-up tables.
gt; gt; gt;
gt; gt; gt; Each table is defined as a Name. In each row of my main worksheet, I can use
gt; gt; gt; data to calculate the Name of its relevant table as text.
gt; gt; gt;
gt; gt; gt; But how can I look up the table?
gt; gt; gt;
gt; gt; gt; In other words, is there a function like INDIRECT that will return a value
gt; gt; gt; that VLOOKUP will treat as a Name for its first argument?

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.

quot;Kevin Vaughnquot; wrote:

gt; To answer your 2nd question first:
gt;
gt; =VLOOKUP(B6, INDIRECT(quot;'quot; amp; A6 amp; quot;'!b14:c15quot;),2,FALSE)
gt;
gt; As far as it not working, it should. My range names look like this:
gt;
gt; Table1=Sheet1!$C$6:$D$9
gt; table2=Sheet1!$F$13:$G$16
gt; and it works fine for me. I would guess something else is causing the
gt; #value error. What is your complete formula? and what do your named ranges
gt; look like?
gt;
gt;
gt;
gt; --
gt; Kevin Vaughn
gt;
gt;
gt; quot;Hershmabquot; wrote:
gt;
gt; gt; I had already tried using INDIRECT like that, with A14 (in your example)
gt; gt; containing the Named range as text. Unfortunately it does not work - the
gt; gt; return value is #VALUE.
gt; gt;
gt; gt; A related question is how to reference a cell in another worksheet, where
gt; gt; the worksheet name is variable.
gt; gt;
gt; gt; quot;Kevin Vaughnquot; wrote:
gt; gt;
gt; gt; gt; Are you talking about named ranges (sometimes referred to as Named Formulae?)
gt; gt; gt; If so, when you are calculating the name of the table are you returning it
gt; gt; gt; to a cell. If so again, then you can use Indirect like so. Say the named
gt; gt; gt; range of the table is in column A and the lookup value is in column B, then a
gt; gt; gt; formula like the following should work:
gt; gt; gt;
gt; gt; gt; =VLOOKUP(B14, INDIRECT(A14), 2, FALSE)
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; Kevin Vaughn
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Hershmabquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; My application has a large number of related look-up tables.
gt; gt; gt; gt;
gt; gt; gt; gt; Each table is defined as a Name. In each row of my main worksheet, I can use
gt; gt; gt; gt; data to calculate the Name of its relevant table as text.
gt; gt; gt; gt;
gt; gt; gt; gt; But how can I look up the table?
gt; gt; gt; gt;
gt; gt; gt; gt; In other words, is there a function like INDIRECT that will return a value
gt; gt; gt; gt; that VLOOKUP will treat as a Name for its first argument?

You are welcome. Glad it worked for you.
--
Kevin Vaughnquot;Hershmabquot; wrote:

gt; I tried the complete VLOOKUP formula - and it worked correctly! What I do not
gt; understand is why the formula =INDIRECT(A14) returns #VALUE. But I will not
gt; pursue this point as it is of no practical consequence.
gt;
gt; Thanks for persuading me to try again.
gt;
gt; quot;Kevin Vaughnquot; wrote:
gt;
gt; gt; To answer your 2nd question first:
gt; gt;
gt; gt; =VLOOKUP(B6, INDIRECT(quot;'quot; amp; A6 amp; quot;'!b14:c15quot;),2,FALSE)
gt; gt;
gt; gt; As far as it not working, it should. My range names look like this:
gt; gt;
gt; gt; Table1=Sheet1!$C$6:$D$9
gt; gt; table2=Sheet1!$F$13:$G$16
gt; gt; and it works fine for me. I would guess something else is causing the
gt; gt; #value error. What is your complete formula? and what do your named ranges
gt; gt; look like?
gt; gt;
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Kevin Vaughn
gt; gt;
gt; gt;
gt; gt; quot;Hershmabquot; wrote:
gt; gt;
gt; gt; gt; I had already tried using INDIRECT like that, with A14 (in your example)
gt; gt; gt; containing the Named range as text. Unfortunately it does not work - the
gt; gt; gt; return value is #VALUE.
gt; gt; gt;
gt; gt; gt; A related question is how to reference a cell in another worksheet, where
gt; gt; gt; the worksheet name is variable.
gt; gt; gt;
gt; gt; gt; quot;Kevin Vaughnquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Are you talking about named ranges (sometimes referred to as Named Formulae?)
gt; gt; gt; gt; If so, when you are calculating the name of the table are you returning it
gt; gt; gt; gt; to a cell. If so again, then you can use Indirect like so. Say the named
gt; gt; gt; gt; range of the table is in column A and the lookup value is in column B, then a
gt; gt; gt; gt; formula like the following should work:
gt; gt; gt; gt;
gt; gt; gt; gt; =VLOOKUP(B14, INDIRECT(A14), 2, FALSE)
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; Kevin Vaughn
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Hershmabquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; My application has a large number of related look-up tables.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Each table is defined as a Name. In each row of my main worksheet, I can use
gt; gt; gt; gt; gt; data to calculate the Name of its relevant table as text.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; But how can I look up the table?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; In other words, is there a function like INDIRECT that will return a value
gt; gt; gt; gt; gt; that VLOOKUP will treat as a Name for its first argument?