Stretching Vlookup to the max... (1 Viewer)

[Chris RR]

Hi,
I am pretty rusty at Excel, but I have been trying to create a menu application (real breakfast, lunch, dinner menus). The basic idea is to let the dietitians enter the date, meal period (breakfast, lunch, dinner) and the meal item on the first sheet in the workbook. There's an intermediate worksheet (invisible to the user) with calcs & whatnot. Then on the last sheet I format a pretty, printable calendar grid, with Monday through Friday across the top and menu items down.

The lookups and validations & other stuff...that's all working...the problem is in filling in the weekday grid.

I calculate a grid location for each item on the second worksheet, and here's the first two colums of that sheet:

Menu_loc Menu Item
D1 Chicken-Breaded
D1 Potato Rounds
E1 Macaroni & Cheese
E1 Cucumber Coins
E1 Meatloaf
F1 Grapes, Fresh Red
F1 Pizza, Sausage

When I go looking for data for the "E1" location using VLOOKUP, I only get one item...not all three. My grid would work beautifully but you can only get one thing for breakfast.

It seems like I should change the menu_loc I have calculated. I think that I want to end up with a menu_loc like "E11" being "Macaroni & Cheese", "E12" being "Cucumber Coins" and so forth. Then I could add additional lines to my weekday grid and use VLOOKUP to return the specific row.

If I do that, how do I add what's essentially a sequence number to my grid location? Or can I keep what I have and use some clever VLOOKUP trick to return multiple rows?

Thanks!

 

[shades]

Howdy. Without seeing a sample workbook, it is hard to say which is the direction to go. It could be that a dependent drop down list would solve your problem or INDEX/MATCH.

Can you post a sample workbook?
________
PREMIERE

 

[unmarkedhelicopter]

This 'could' be easily done with a UDF, but that would slow your sheet down (and should be used as a last resort.
Why not extend your grid so it has space for (say) the four items ? though this would require an array formula and nearly be as slow as UDF
What happens if a 'person' dosen't like Pizza ?
A sample sheet is the way to go. We can then comment more meaningfully.

 

[Chris RR]

Here's a sample workbook

Hi,
Here's a very simplified copy of my workbook.

Sheet 1 in this sample is where I've taken the user input and calculated the calendar locations for each meal item. There's some cells that do date calculations there, too. (In my real workbook, there is another sheet where the actual data entry occurs.)

Sheet 2 is the calendar grid. I need to be able to put multiple meal items under each day of the month. With VLOOKUP I can only get one item per day, even if I have multiple rows under each date.

And, sorry, the menu is the same for everyone...even if they don't like pizza!

Thanks for your time and interest.

Chris RR

 

[scott-atkinson]

Looking at your spreadsheet, I do not believe that either Index/Match or Vlookup will work in extracting the data.

I wouls suggest, and depending on your VBA skills, write some code, to review each day for a change, and if no change has taken place to return the value in the Menu Item field.

Using Loops and Offset function you should be able to do this.

My coding is not brilliant, so I would suggest posting a question in the Visual Basic section of this Forum.

 

[Chris RR]

Working solution

Hi,
I have a solution using VLOOKUP that seems to be working. For each date, for each meal period, my user can enter up to 10 menu items and I needed to put these into a calendar format.

When I started, I was calculating where, on my calendar, each menu item goes. For example, chicken nuggets for lunch on 1/7/08 would go in the "Monday" column, week 2, and I concatenated the Monday value ("B") with the week number ("2") and put them in a field called Menu_loc ("B2"). When I tried to populate my calendar, VLOOKUP used Menu_Loc and returned the only the first item for each day.

So I expanded Menu_loc by adding the meal period (B for Breakfast, L for Lunch.) Those chicken nuggets now had a Menu_loc of "B2L". VLOOKUP returned one item per meal period per day...better, but not good enough.

Then I added a sequence, and that did the trick. To create the sequence, I check that grid location against the the previous value. If they are equal, I add one to my sequence, otherwise reset it to 1. The formula is pretty simple:
=IF($C2=$C1,$D1+1,1)
This works even on my first data row, which is checked against the column header...a little flaky it works.

I append the sequence to Menu_Loc, so, chicken nuggets end up with a final location something like "B2L1". That day's hash browns will be "B2L2", Tuesday's corn flakes are "C2B1". Each one is unique and VLOOKUP can find them all.

Thanks for everyone's time and help.