vlookup too limiting...

bas_belgium

New Member
Joined
Jul 26, 2010
Messages
4
Hi guys...
I have an issue...
I'm trying to create something of an automatic grocery list everytime I select what dish we will eat for which day of the week...
Let me explain...
I have a sheet where I have the ingredients for each dish:



In a separate sheet I have an input list where I can select what dish I will eat on what day (can be multiple dishes per day, e.g. lunch and dinner)...


What I want to have now automatically on yet another sheet, is a grocery list, which basically looks for the dishes on the weekplanner, and provides all the ingredients of the chosen dishes for that week...


BUT I'm struggling very hard...
VLOOKUP only gives me 1 ingredient out of the ingredient list...

Any suggestions on making this a simple yet very effective project?

Cheers upfront... :)
Regards, Bas
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Stu Dapples

Active Member
Joined
Jan 12, 2009
Messages
252
You need to tell Excel to look for each iteration of the item you are looking for, then return all the offset columns in the following rows. Someone on here sent me the formula to do but I cant access my network drives at the moment so I cant send it to you!

If you search for "return multiple iterations of the same value" you should see a thread I started a few weeks back, there is a formula in there which I believe will do the job for you....
 

bas_belgium

New Member
Joined
Jul 26, 2010
Messages
4
Thanks Stu,
but I am probably really daft at this...
The solution on the post you referred me to doesn't make any sense to me.
I mean, it probably makes a lot of sense, but I just don't understand it...
No idea how that would apply to my tables etc...
You're saying I need to 'reiterate' the vlookup 8 times per dish (since there's about 8 ingredients per dish), and then again reiterate x 7 for the days of the week and reiterate x 3 for the meals per days... That's a lot of vlookup formulas, and they don't easily copy, it's all manual)...
Or am I getting it wrong?
Cheers,
Bas
 

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,733
Are macros out of the question? With a little piece of code you could get this done quite easily.

Also, you might want to change your database a little bit to move the volume numbers in their own cells and type them in the database per serving: if you know you're having friends come over for dinner you're going to need more tomatoes than usually.

This way you could have all the ingredients listed on a single row for the whole week.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,519
Messages
5,837,824
Members
430,517
Latest member
chessypack86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top