Excel workbook that reads two pasted reports and outputs a categorised breakdown, how would you build it?

I'm trying to build an Excel workbook that automates our daily breakfast briefing (context: breakfast service in a hotel). The intention is: every morning I paste two system reports into Tab 2 and Tab 3 (one report per tab), and Tab 1 automatically cross-checks the data from both and gives me a clean breakdown for the breakfast team, no manual work.

The two reports I paste each morning

- Tab 2 — Meal Plan report (BJMPGR): room number, guest name, rate code, meal plan name, PAX count

- Tab 3 — In-House Guest report: room number, guest name, rate code, group/company name, reservation note (e.g. "RB" = company covering room & breakfast, "ALL" = all charges covered by company)

What Tab 1 needs to show — four categories

1. Breakfast included in rate — certain rate codes (BB, MPOBB, WINTER package etc.) always include breakfast. No charge needed.

2. Company pays — post to folio — reservation note in Tab 3 contains "RB" or "ALL" as a whole word. Charge needs posting to company account.

3. $24 access — guest pays — meal plan name in Tab 2 contains "Corporate Discounted Breakfast" or similar keywords. Guest pays $24/cover.

4. Pre-booked — already posted — guest added breakfast themselves. Revenue already in their account. No action needed.

Complications making this tricky

- The WINTER package creates two rows per guest in the meal plan report (breakfast + barista coffee) — the coffee row must be excluded so covers aren't double-counted

- PAX column format is "2 / 0" — needs parsing to extract the number

- Room numbers come through as integers in one report and need to match as text across both via lookup

- Rate codes and meal plan keyword names change seasonally — ideally the rules should be editable from a separate settings tab without touching any formulas

Questions

1. What's the cleanest way to cross-reference two pasted tables and classify rows based on values from both — XLOOKUP + IF chains, or something better?

2. For "first match wins" keyword lookup against a settings table, is AGGREGATE(15,6, ROW/ISNUMBER(SEARCH(...))) the right approach, or is there a simpler method?

3. Would Power Query handle this more cleanly than formulas for a daily paste-and-refresh workflow?

4. Any thoughts on the overall structure — paste zones in Tabs 2 & 3, hidden logic tab doing the classification, display tab pulling results?

5. If on Excel 365, would FILTER + SORT be cleaner than AGGREGATE for pulling and sorting rows per category into Tab 1?