Using VLOOKUP with abitlity to choose from multiple defined names.

[Armando]

I have created multiple defined names or tables in Excel (e.g. Table1,
Table2, etc.)
Each table contains the ingredients and measurements for each recipe.

Using vlookup I have defined the lookup value as the Recipe# and based on
that would like to link the defined table range for the corresponding
Recipe#. For example, if I specify "1" as recipe#1 the vlookup should refer
to the defined name "Table1" and return the ingredient for the default column.

Example (two worksheets contain):
Recipe#1
Table1
A B C
1 can tomato
1 lb beef

Recipe#2
Table2
A B C
2 lbs chicken breasts
1 med onion

The third worksheet (or main wks) contains the grocery list
Lookup Recipe# = 1 (cell A1)
On a separate cell (C2) I put in a formula to concatenate "Table" and "1"
resulting in "Table1" to be used as an array in my vlookup formula:
vlookup(A1,C2,1) which should "1" in column A, row 1; vlookup(A1,C2,2)=can;
vlookup(A1,C2,1)=tomato and so forth, resulting as follows:
A B C
1 can tomato
1 lb beef
Obviously lookup formulas do not accept cell references as defined
names/arrays and it would return "#VALUE!" and this setup does not work for
me.
Would appreciate a solution.

[davesexcel]

e-mail me a short example and I will see if I can help you

[Ardus Petus]

Have a look at INDIRECT

HTH
--
AP

"Armando" <[email protected]> a écrit dans le message de
news:[email protected]...
> I have created multiple defined names or tables in Excel (e.g. Table1,
> Table2, etc.)
> Each table contains the ingredients and measurements for each recipe.
>
> Using vlookup I have defined the lookup value as the Recipe# and based on
> that would like to link the defined table range for the corresponding
> Recipe#. For example, if I specify "1" as recipe#1 the vlookup should
refer
> to the defined name "Table1" and return the ingredient for the default
column.
>
> Example (two worksheets contain):
> Recipe#1
> Table1
> A B C
> 1 can tomato
> 1 lb beef
>
> Recipe#2
> Table2
> A B C
> 2 lbs chicken breasts
> 1 med onion
>
> The third worksheet (or main wks) contains the grocery list
> Lookup Recipe# = 1 (cell A1)
> On a separate cell (C2) I put in a formula to concatenate "Table" and "1"
> resulting in "Table1" to be used as an array in my vlookup formula:
> vlookup(A1,C2,1) which should "1" in column A, row 1;
vlookup(A1,C2,2)=can;
> vlookup(A1,C2,1)=tomato and so forth, resulting as follows:
> A B C
> 1 can tomato
> 1 lb beef
> Obviously lookup formulas do not accept cell references as defined
> names/arrays and it would return "#VALUE!" and this setup does not work
for
> me.
> Would appreciate a solution.
>

[Armando]

Thank you, but it didn't work. Maybe I'm not using it in the right context.
I used it as: vlookup(cellref,indirect(c2),1). It works if I use it only as
=indirect(c2) and it would return Table1.

"Ardus Petus" wrote:

> Have a look at INDIRECT
>
> HTH
> --
> AP
>
> "Armando" <[email protected]> a écrit dans le message de
> news:[email protected]...
> > I have created multiple defined names or tables in Excel (e.g. Table1,
> > Table2, etc.)
> > Each table contains the ingredients and measurements for each recipe.
> >
> > Using vlookup I have defined the lookup value as the Recipe# and based on
> > that would like to link the defined table range for the corresponding
> > Recipe#. For example, if I specify "1" as recipe#1 the vlookup should
> refer
> > to the defined name "Table1" and return the ingredient for the default
> column.
> >
> > Example (two worksheets contain):
> > Recipe#1
> > Table1
> > A B C
> > 1 can tomato
> > 1 lb beef
> >
> > Recipe#2
> > Table2
> > A B C
> > 2 lbs chicken breasts
> > 1 med onion
> >
> > The third worksheet (or main wks) contains the grocery list
> > Lookup Recipe# = 1 (cell A1)
> > On a separate cell (C2) I put in a formula to concatenate "Table" and "1"
> > resulting in "Table1" to be used as an array in my vlookup formula:
> > vlookup(A1,C2,1) which should "1" in column A, row 1;
> vlookup(A1,C2,2)=can;
> > vlookup(A1,C2,1)=tomato and so forth, resulting as follows:
> > A B C
> > 1 can tomato
> > 1 lb beef
> > Obviously lookup formulas do not accept cell references as defined
> > names/arrays and it would return "#VALUE!" and this setup does not work
> for
> > me.
> > Would appreciate a solution.
> >
>
>
>

[L. Howard Kittle]

Hi Armando,

Perhaps this. You were using 1 instead of 2 for the column look up which is
the same column the lookup values are in. Adding the 4th argument, 0,
demands an exact match

vlookup(cellref,indirect(c2),2,0).

If you want I will look at a sample workbook and give it a go.
(leave some recipes in the workbook...<vbg>)

HTH
Regards,
Howard

"Armando" <[email protected]> wrote in message
news:[email protected]...
>I have created multiple defined names or tables in Excel (e.g. Table1,
> Table2, etc.)
> Each table contains the ingredients and measurements for each recipe.
>
> Using vlookup I have defined the lookup value as the Recipe# and based on
> that would like to link the defined table range for the corresponding
> Recipe#. For example, if I specify "1" as recipe#1 the vlookup should
> refer
> to the defined name "Table1" and return the ingredient for the default
> column.
>
> Example (two worksheets contain):
> Recipe#1
> Table1
> A B C
> 1 can tomato
> 1 lb beef
>
> Recipe#2
> Table2
> A B C
> 2 lbs chicken breasts
> 1 med onion
>
> The third worksheet (or main wks) contains the grocery list
> Lookup Recipe# = 1 (cell A1)
> On a separate cell (C2) I put in a formula to concatenate "Table" and "1"
> resulting in "Table1" to be used as an array in my vlookup formula:
> vlookup(A1,C2,1) which should "1" in column A, row 1;
> vlookup(A1,C2,2)=can;
> vlookup(A1,C2,1)=tomato and so forth, resulting as follows:
> A B C
> 1 can tomato
> 1 lb beef
> Obviously lookup formulas do not accept cell references as defined
> names/arrays and it would return "#VALUE!" and this setup does not work
> for
> me.
> Would appreciate a solution.
>

[Armando]

Hi L. Howard,

That worked great! Thank you! That 4th argument, 0, was the one missing for
it to work.

Sure thing, if you're interested be glad to send the file anyway. Will send
the Excel file to your address @comcast.net. It's not much in the file now
since I just began to build it, but you're welcome to try them. :-)

"L. Howard Kittle" wrote:

> Hi Armando,
>
> Perhaps this. You were using 1 instead of 2 for the column look up which is
> the same column the lookup values are in. Adding the 4th argument, 0,
> demands an exact match
>
> vlookup(cellref,indirect(c2),2,0).
>
> If you want I will look at a sample workbook and give it a go.
> (leave some recipes in the workbook...<vbg>)
>
> HTH
> Regards,
> Howard
>
> "Armando" <[email protected]> wrote in message
> news:[email protected]...
> >I have created multiple defined names or tables in Excel (e.g. Table1,
> > Table2, etc.)
> > Each table contains the ingredients and measurements for each recipe.
> >
> > Using vlookup I have defined the lookup value as the Recipe# and based on
> > that would like to link the defined table range for the corresponding
> > Recipe#. For example, if I specify "1" as recipe#1 the vlookup should
> > refer
> > to the defined name "Table1" and return the ingredient for the default
> > column.
> >
> > Example (two worksheets contain):
> > Recipe#1
> > Table1
> > A B C
> > 1 can tomato
> > 1 lb beef
> >
> > Recipe#2
> > Table2
> > A B C
> > 2 lbs chicken breasts
> > 1 med onion
> >
> > The third worksheet (or main wks) contains the grocery list
> > Lookup Recipe# = 1 (cell A1)
> > On a separate cell (C2) I put in a formula to concatenate "Table" and "1"
> > resulting in "Table1" to be used as an array in my vlookup formula:
> > vlookup(A1,C2,1) which should "1" in column A, row 1;
> > vlookup(A1,C2,2)=can;
> > vlookup(A1,C2,1)=tomato and so forth, resulting as follows:
> > A B C
> > 1 can tomato
> > 1 lb beef
> > Obviously lookup formulas do not accept cell references as defined
> > names/arrays and it would return "#VALUE!" and this setup does not work
> > for
> > me.
> > Would appreciate a solution.
> >
>
>
>