This is a bit of a complicated problem, so hopefully I'm not about to fail miserably at explaining it.

I'm creating a Profit guide for a game. In the game, there are "Recipes" that can be made with various items. A given recipe can have one to five ingredients.

Each Recipe has a sale price, and each ingredient has a sale price. Essentially, I'm trying to create a spreadsheet that shows the profit made from cooking the recipe, compared to just selling the ingredients.

Ok, so maybe that wasn't that complicated. The problem comes in that there are at least 173 possible ingredients, and 227 recipes. Not only that, but most of the recipes can actually be made with different combinations of ingredients, and I'd like to look at the profit for every combination. I'd bet there are at least a couple thousand recipes when you factor in each possible combination.

What I have now are two sheets, one for Ingredients and one for Recipes. The Ingredients sheet has 173 rows, with ingredient names in column A and ingredient prices in column B. The Recipes sheet has 227 rows, with recipe names in column A and recipe prices in column B.

As far as what ingredients create each recipe, I've got that in a spreadsheet with recipe names in column A and ingredients in column B - so for a recipe named "Apple Pie" with ingredients "Apple, Pie Crust, Whipped Cream", there are three rows in this sheet - "Apple Pie" in column A of each row, and one ingredient in column B of each row. I don't know if this sheet will be useful (it was created for something else), so I know I might have to re-type the recipes themselves.

So, with my limited Excel knowledge, the only way I can figure to do it is to create a new spreadsheet with 13 columns - Recipe Name and Price, five Ingredient Name columns, five Ingredient Price columns, and the Profit column. Then, I'd go recipe-by-recipe, type in the ingredients, find the ingredients in the Ingredients spreadsheet, put in the references, etc.

Hopefully this had made sense. Anyway, can anyone think of any better/faster ways to do this? It seems counter-intuitive that I have all the data in spreadsheets yet can't formulaically manipulate it into what I'm looking for.