Powerquery

1. Best & Cleanest Method → Power Query in Excel (Recommended)

Steps:

1. Open Excel → new blank workbook

2. Put both lists into separate sheets:

   • Sheet1 = "PROD" Column A: schema Column B: table_name (first row = headers: Schema, TableName)
   • Sheet2 = "LOWER" Column A: schema Column B: table_name (headers: Schema, TableName)

3. Create helper column with full name (very important!):

   In both sheets, add column C with formula:

   text

   =A2&"."&B2

   (copy down)

   Now you have:

   • PROD!C → full name like IDBS_APP.ORDER_HEADER
   • LOWER!C → same format

4. Go to Data → Get Data → From Other Sources → Blank Query

5. In Power Query Editor:

   powerquery

   let
       Prod = Excel.CurrentWorkbook(){[Name="PROD"]}[Content],
       Lower = Excel.CurrentWorkbook(){[Name="LOWER"]}[Content],
   
       #"Merged Queries" = Table.NestedJoin(Prod, {"C"}, Lower, {"C"}, "LOWER", JoinKind.LeftAnti),
       #"Expanded LOWER" = Table.ExpandTableColumn(#"Merged Queries", "LOWER", {}, {}),
   
       #"Removed Duplicates" = Table.Distinct(#"Expanded LOWER"),
       #"Sorted" = Table.Sort(#"Removed Duplicates",{{"C", Order.Ascending}})
   in
       #"Sorted"

6. Click Close & Load → new sheet with only tables that exist in PROD but NOT in lower env

7. (Optional) Do the opposite (extra in lower env) → change LeftAnti to RightAnti

This method is:

• Clean
• Repeatable
• Doesn't break when you refresh data

2. Fast Classic Excel Method (VLOOKUP / XLOOKUP)

In a new sheet:

A1:D1 headers: PROD_Schema | PROD_Table | Status | Comment

Put PROD list in columns A:B

Then in C2 (status):

excel

=IF(ISNA(XLOOKUP(A2&"."&B2, LOWER!$A:$A&"."&LOWER!$B:$B, "Exists", "Missing")) ,"MISSING in lower env" ,"Exists in both")

Or simpler (classic style):

excel

=IF(ISNA(VLOOKUP(A2&"."&B2, LOWER!C:C, 1, FALSE)), "MISSING", "Exists")

Filter by "MISSING" → you have your report.

3. Super Fast Python One-liner Approach (if you can run Python)

Python

import pandas as pd

prod = pd.read_excel("tables_prod.xlsx", header=None, names=["schema","table"])
lower = pd.read_excel("tables_lower.xlsx", header=None, names=["schema","table"])

prod['full'] = prod['schema'] + '.' + prod['table']
lower['full'] = lower['schema'] + '.' + lower['table']

missing_in_lower = prod[~prod['full'].isin(lower['full'])]

# Save result
missing_in_lower[['schema','table']].to_excel("missing_in_lower.xlsx", index=False)
print(f"Found {len(missing_in_lower)} tables missing in lower env")

Just adjust file names/paths.

Summary – What I Would Do in Your Place

Situation	Recommended choice
You want nice Excel + repeatable	Power Query (method 1)
One-time, don't want to learn new	XLOOKUP / VLOOKUP (method 2)
You already use Python / automate	pandas script (method 3)
You want fastest possible (no Excel)	Text files + comm or PowerShell Compare-Object

Most people in DBA/developer roles end up using Power Query for this kind of comparison because it's visual, powerful, and built into modern Excel.

Let me know which method you prefer and I can give you more detailed step-by-step (with screenshots description if needed).