# How to use LOOKUPs

#### concretemonkey

##### Board Regular
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.

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### MarkAndrews

##### Well-known Member
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

##### Well-known Member
=HLOOKUP(\$A\$1,\$C\$2:\$W\$58,ROW(),0)

A1 = Receipe name

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

Hope this helps

#### Wret

##### New Member
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.

Replies
1
Views
137
Replies
0
Views
336
Replies
5
Views
323
Replies
4
Views
166
Replies
3
Views
502

1,191,353
Messages
5,986,162
Members
440,007
Latest member
cjw29209

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back