If a cell contains text (from another worksheet), populate another cell with...

[amberleemay]

I am trying to make a food cost worksheet that will calculate how much a recipe is costing me to make. I currently have two worksheets in my document. Sheet 1 is called "Ingredients Cost" and Sheet 2 is called "Fettuccine Alfredo" (my first recipe I am analyzing).

In my "Ingredients Cost" sheet I have the name of the ingredient as well as cost information (package size, serving size, cost per serving, etc.). In my "Fettuccine Alfredo" sheet I have several fields - Ingredient (which I will populate the name from the "Ingredients Cost" sheet (i.e. ='Ingredients Cost'!A11). I will manually enter the recipe information (how much I need of the ingredient). I then need the "Cost Per Unit" to populate from "Ingredients Cost" sheet based on the ingredient I chose.

In other words, I need a formula that would know that cell A3 on sheet "Fettuccine Alfredo" (my ingredient) is pulling from cell A11 on sheet "Ingredients Cost" - and would then populate cell D3 on sheet "Fettuccine Alfredo" with cell I11 on sheet "Ingredients Cost."

I am currently having to do this manually for each ingredient and it would be a huge time saver to have a formula. Any help would be greatly appreciated!!!! Thank you so much!

[davesexcel]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Special-K]

Try

in "Fettuccine Alfredo"!D3
=INDEX("Ingredients Cost"!A1:I100,MATCH(A3,"Ingredients Cost"!A1:A11,0),9)

[amberleemay]

Thank you for the quick responses. I have attached my worksheet with only the data I mentioned in the post.

Recipe Calculator Spreadsheet.xls

[amberleemay]

Special-K - That is REALLY CLOSE! I was very excited when I tried it with the Fettuccine - worked perfectly. I even dragged the formula down a few ingredients and it worked for them as well. For some reason it did not work on a few of the ingredients. I have attached the more detailed spreadsheet so you can see what I am talking about.

Recipe Calculator 2.xls

[amberleemay]

Yay! I think I figured it out - it seems to be working now! I made a couple of minor adjustments to the code that Special-K provided.

Original: =INDEX("Ingredients Cost"!A1:I100,MATCH(A3,"Ingredients Cost"!A1:A11,0),9)

Modified: =INDEX('Ingredients Cost'!$A$1:$I$100,MATCH(A3,'Ingredients Cost'!$A$1:$A$11,0),9)