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.

 

[Ardus Petus]

Have a look at INDIRECT

HTH

 

[davesexcel]

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

 

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

 

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

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.