I am trying to create a recipe book in Excel 2013, that has a "main page" where you choose what recipe you want, and it shows you the recipe and ingredients.
I created two dependent drop down lists: in the first one you choose from Breakfast, Brunch, Lunch, Dinner, Supper etc., and once you choose from it, the other drop down list allows you to choose from recipes filed under the main category, I used the INDIRECT function with data validation to achieve that and it works perfectly.
The problem appears when I try to write a relative formula that shows ingredients depending on what you chose from the second drop down list (cell C3). I have the ingredients written down on a separate sheet: (sheet Ingredients). I have the names of recipes in column A, and the ingredients in column B. I can't figure out how to write the formula without having to manually elongate it with each recipe I add. What I got now is:
=IF(C3="Pancakes"; INDEX(Ingredients!B:B; MATCH("Pancakes"; Ingredients!A:A;0)); IF(C3="Banana Oatmeal"; INDEX(Ingredients!B:B; MATCH("Banana Oatmeal"; Ingredients!A:A;0)); IF(C3="Millet Porridge"; INDEX(Ingredients!B:B; MATCH("Millet Porridge"; Ingredients!A:A; 0)); "Not Found")))
My question is: is there a way to write it like =IF(C3="any x"; INDEX(Ingredients!B:B; MATCH("any x"; Ingredients!A:A;0)); "Not Found") and end it at that.
I will much appreciate all ideas!
Bookmarks