how to lookup one value to set a range for another lookup

[jgreene11]

How do I write a lookup that will search for the revenue center then drop down to find food then over to pull the value from a meal period, on the reference sheet there are multiple revenue centers and therefore multiple cells with the word food in them?

Net Sales For Revenue Center HTREST
	Breakfast	Lunch	Dinner	Late Night	Grand Ttl
FOOD	600​	282.4​	2,378.30​	0​	3,260.70​
N/A BEV	0​	45.25​	71.25​	0​	116.5​
LIQUOR	0​	0​	384.25​	0​	384.25​
BEER	0​	0​	112​	0​	112​
	0​	0​	0​	0​	0​
	0​	0​	0​	0​	0​
WINE	0​	0​	250​	0​	250​

 

[Shloime]

example for reference sheet please

 

[awoohaw]

I don't see the word food at all in your table. Also, please use the xl2bb add in (link below) to post a miniworksheet. And, a sample of expected outcome(s) would be great!

 

[jgreene11]

So the above example is a snip of a single day report. The goal is to be able to automate the loading of the information from the daily reports into spread sheets that break out performance over different time periods.
what I am looking to do is create a lookup that will go to the specific revenue center, the one in the above is HTREST( there are 9 different revenue centers on the daily sheet), then drop search below the specified revenue center to find the keyword such as "food" in column A, then across the row to column C and display that value. I cannot just lookup food as it occurs 9 times on the sheet.

 

[KRice]

Could you clarify which version of Excel you are using?

 

[awoohaw]

So the above example is a snip of a single day report. The goal is to be able to automate the loading of the information from the daily reports into spread sheets that break out performance over different time periods.
what I am looking to do is create a lookup that will go to the specific revenue center, the one in the above is HTREST( there are 9 different revenue centers on the daily sheet), then drop search below the specified revenue center to find the keyword such as "food" in column A, then across the row to column C and display that value. I cannot just lookup food as it occurs 9 times on the sheet.

Please help the forum help you. Please post enough data that the forum can work through all your scenarios. Is each section separated by the same number of rows? Is there a key word that starts each section? Have you looked into using Power Query to process your onboarding of data? Do you store each days reports individually in a daily report folder? Are the files names similar? Are other files/reports that do not pertain to this data in that folder?

And knowing what version of excel you use would be very helpful. (Please update your profile with that information so it is shown with your chat header avatar/buttons).

There are many youtube tutorials on Power Query that can help you process your data more efficiently than copy/paste data and then doing lookups.
Please search for Power Query in these Youtube channels: Mr. Excel, ExcelIsFun, Leila Gharani, and MyOnlineTrainingHub.

Here is a complete playlist by MyOnlineTrainingHub that can help you in short time:

https://www.youtube.com/playlist?list=PLmd91OWgLVSKnVrL0YxdOH61MAiqlFHac

 

[KRice]

@awoohaw has offered some excellent advice. I would also suggest providing some idea about what you would like the final result to look like? For example, are you trying to construct a summary table where all of the "Food" entries for a particular meal type are summed across all Revenue Centers, or just one? Based on your comment about this being a daily report, I like the idea of using Power Query, but am not sure your Excel version supports it (which is why that information is critical).

And since this represents a daily report, are you interested in capturing the date and assigning it to these records so that you can evaluate performance over time? Where would the date of the daily report be found?

 

[jgreene11]

Could you clarify which version of Excel you are using?

when I open it is listing as 365

 

[awoohaw]

when I open it is listing as 365

Will you be posting some data?

 

[jgreene11]

@awoohaw has offered some excellent advice. I would also suggest providing some idea about what you would like the final result to look like? For example, are you trying to construct a summary table where all of the "Food" entries for a particular meal type are summed across all Revenue Centers, or just one? Based on your comment about this being a daily report, I like the idea of using Power Query, but am not sure your Excel version supports it (which is why that information is critical).

And since this represents a daily report, are you interested in capturing the date and assigning it to these records so that you can evaluate performance over time? Where would the date of the daily report be found?

The daily report is an excel document that is generated by the point of sale system. I then just rename the file and set it into a folder on my computer, so all of the reports would be titles as Rev 10-29-23, Rev 10-30-23, Rev 10-31-23 etc.

It appears as if all of the revenue centers are like the copy in the first posting and it food is 4 rows below revenue center, N/A Bev is 5 rows below revenue center, Liquor is 6 rows below revenue center and the meal periods all line up as 1 column over for breakfast, 2 columns over for lunch, 3 columns over for dinner.

I have tried setting up the XL2BB, but the work computer keeps coming back with not allowed in protected mode.

Yes the design is that it looks at the performance of each of the attributes of sale across all of the revenue centers per meal period as well as per day, and then totaled up per week, so that in the end it is showing the percentage revenue during the meal period per revenue center, the revenue per cover, as well as the revenue per occupancy.