Search Multifacitated Table for Values

I'm attempting to search values from a set of data that's downloaded every day. Example below. I need to find out how much of item category (food or bev) was sold during lunch and dinner in both the bar and the dining room. I tried using index match but I'm having trouble since there essentially 2 tables located on top of each other.


Things to note:

1. The format sucks, I know. But it is downloaded this way...

2. Goal is to pull data from "Feb-26" (daily tab) to the "Week 1" tab.

3. There are 3 criteria

Meal time: Lunch or Dinner (There sometimes are more)

Revenue Center: Bar or Dining Room (There sometimes are more)

Item Category: Food or Beverage


Can you suggest a formula I could use to scan all the data and find the correct values?

The range I'd be searching for, Bar or Dining room, would always begin with the cell containing Bar/Dining Room and end with the cell containing "Total Net Sales". Could I somehow define the range using that and then use index/match?

example.xlsx

Without seeing your excelfile usually you solve this with a pivot tabel

Popipipo,

I've attached an example of the data download I do every day. "Feb-26" contains the download and "week 1" is the sheet that I have to manually enter the values into. Ideally I'd like the "week 1" tab to read the "Feb-26" data when I paste it in there.

Here are a couple of different solutions that you may be able to adapt to your situation. This uses a vertical arrangement of dates so that the data can be more easily read and manipulated.

Horizontal arrangements are more difficult to read and are more cumbersome to calculate with.

The formulas in worksheet week1 refer to another workbook.
The index formula don't like that.

I've been looking for more 15 min to the file and don't know what you exactly want.

You are looking for these items...
Food
Liquor
Beer
Wine
Cold Bevs

Could you change those names to something actually matches what you want from the douwnload? You ask for "Liquor", but what you actually want (based on your sample) is "Beverage Total Net Sales" and you say Beer, but your total is for LIQUOR


They seem to be in the same relative position in each "Table" of data. Is that true for all the downloads?