# Excel - bring over a cell's value based on another selection from that row

I'm creating a restaurant pricing spreadsheet. On one sheet, I have a list of all ingredients to order with the cost per size next to it in each row. The data on this sheet looks like this:

1 Brisket      \$.50
2 Chicken    \$.24
3 Beef          \$.36

There are about 100 items....Now, on another sheet, I have the first menu item which will be a combination of several ingredients from the sheet I mention above....I created a dropdown list for the ingredient, but I want the cost to automatically load in the next column based on the ingredient I selected...So, if on the 2nd sheet, I selected "Beef" as the first ingredient, the cell next to that dropdown would load "\$.36". How can I do this?
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
You can use this formula..assuming you are picking values in cell a2 in sheet1 from sheet2 where in A Column you have item and b column you price...

Use this formula in B1..

=VLOOKUP(Sheet1!a1,Sheet2!A:B,2,0)

This will select the price as soon you select the quantity..

Otherwise it will give you an error..if you want to hide that error too then use this...

=iferror(VLOOKUP(Sheet1!a1,Sheet2!A:B,2,0),"")

Saurabh...
Use a combination of INDEX and MATCH to find the value you need. VLOOKUP requires that your lookup table be in sorted order, while INDEX and MATCH do not.
Commented:
Shaun,

If you are looking an exact match it doesn't require that your data is sorted like the example he posted a vlookup will work perfectly without any problem..

Saurabh...
Author Commented:
Will the vlookup dynamically bring over the cost for that particular item over to the second sheet based on what ingredient I select? It looks like it's selecting from a range.
Commented:
From the range it will pick the items only which matches you criteria at sheet1...

Saurabh...
EngineerCommented:
Try this file
RecipeCalculator.xlsx

Experts Exchange Solution brought to you by