I am trying to build a workbook that will allow be to enter a value in one sheet, and then have formulas use that value to lookup associated values with the originally entered value from a separate sheet.
I am currently working on a meal planner tool, so will use this as example data for your review (though, I have certainly run into this previously, which is why I am pinging the community for help now :-)
So, please consider a starting data set something like:
I want to be able to be on another sheet and choose from a drop down list (I know how to use data validation for this) the 'Meal' - then I would like the other sheet to populate the ingredients needed based on this starting data set. I have been trying to use combinations of VLOOKUP, INDEX, MATCH and tinkered with Pivot Tables a bit, but, cannot get it. The issue I have been running into is that in starting data set, the first column has many entries of the same value (as I have it currently organized).
An example of what I would like to achieve programaticaly is provided as an example below, where the 'Meal' would be chosen for each day (red text) and then the ingredients, qty, and unit would auto-populate in respective locations.
The ultimate goal is to be able to pick and choose meals for each day of the coming week and then have ingredient information populate - with all meals and ingredient info in a database sheet. Thanks in advance for any insight available.