How to use LOOKUPs

[concretemonkey]

I have a speadsheet with multiple tables. One table contains a list of ingredients for a recipe, and the other sheet contains empty cells.

The recipe sheet is complete, and is called recipies. I want Sheet 2 to lookup the product code and insert the recipie for it.

Example.
The product codes are in cells B2 - W2 on the sheet called Recipies. The ingredient values are in the cells C2:W58 and are numbers.

On sheet2, I want to be able to type in the product code on cell A1 and then in cells A2:A57 the figures that correspond with the product code on the sheet Recipies is automatically inserted. So if the code matches, and there is a figure in one of the cells below the recipie code, it would be put into the list on my other sheet (sheet2).

I think I need to use LOOKUPS but I have no idea what I am going to be using for this. Is it a HLOOKUP or a VLOOKUP? Will it require and individual code to be inserted into each cell on Sheet2 or will one line of code complete the task?

Any help would be more than appreciated, as I am stuck on this one.

 

[MarkAndrews]

You could use something like this

=HLOOKUP($I$1,$A:$A,ROW(),0)

Row() is a variable, IE - row() placed in row 2 of a spreadsheet, will make the lookup look specifically at row 2

Row() planed in row 3 = 3 etc

Similar to -

=HLOOKUP($I$1,$A:$A,2,0)

Modify the cell range A1:W500 (eg) as required & also change the lookup value

 

[MarkAndrews]

=HLOOKUP($A$1,$C$2:$W$58,ROW(),0)

A1 = Receipe name

$C$2:$W$58 = range to look in

Row() - ascend this down your spreadsheet

Hope this helps

 

[Wret]

I use a recipe spreadsheet very similar to what you described. Sheet 1 is a list of recipes by item number in column A. The other columns are identified ingredient 1, ingredient 1%, ingredient 2, ingredient 2%, etc. I have space available for a possible 11 ingredients but most do not use all 11.

Sheet 2 has a place for recipe item number and batch weight at the top. It has a columns for ingredient name, percentage and weitght (lets call them C,D, and E). The formula in c1 is =LOOKUP($a$1,'Sheet 1'!$A:$A,'Sheet 1'!B:B) and returns the name of the 1st ingredient. The formula in D1 is =LOOKUP($a$1,'Sheet 1'!$A:$A,'Sheet 1'!c:c) and returns the % of the first ingredient. The formula in E1 is =$b$1*C1 which calculates the ingredient amount needed for the batch. These formulas are copied to row 11 for 11 possible ingredients.