Tables, Structured References, and XLOOKUP

💡 Key concepts

• Excel Tables (Ctrl+T): Auto-expanding ranges, named columns (Milestones[ForecastDate]), calculated columns, total rows. This is the foundation for everything that follows, including Power Query and Power Automate (the Excel connector in Power Automate requires tables).
• XLOOKUP syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
  • if_not_found: "Not in contract list" instead of #N/A
  • match_mode -1/1: next smaller/larger. Useful for threshold bands (e.g., CPI → rating).
  • search_mode -1: search from the bottom, returning the latest record for a key
  • Returning multiple columns: =XLOOKUP([@ProgramID], Programs[ProgramID], Programs[[ProgramName]:[Portfolio]])
• Two-way lookup: nest XLOOKUP in XLOOKUP, or use INDEX/XMATCH.
• Data hygiene: TRIM, CLEAN, VALUE, TEXT, DATEVALUE. Watch out for numbers stored as text.
• Fiscal year formula: =YEAR([@Date]) + (MONTH([@Date])>=10) gives the federal FY.

🛠 Hands-on exercise: Enrich the Milestone Tracker

Data: milestones (MilestoneID, ProgramID, MilestoneName, BaselineDate, ForecastDate, ActualDate, Owner) and programs (ProgramID, ProgramName, Portfolio, ProgramManager).

1. Convert both to Tables named Milestones and Programs.
2. Add calculated columns:
   • ProgramName and Portfolio via one XLOOKUP returning 2 columns (use a spill-friendly layout or two XLOOKUPs inside the table)
   • SlipDays = [@ForecastDate] - [@BaselineDate]
   • FY_Baseline using the fiscal-year formula
   • Status = "Complete" if ActualDate is filled, "Late" if ForecastDate < TODAY() and not complete, "At Risk" if SlipDays > 30, otherwise "On Track"
3. Build a lookup cell where you type a ProgramID and get its most recent completed milestone (XLOOKUP with search_mode -1 on a sorted table, or use MAXIFS + XLOOKUP).
4. Build a CPI rating band table (0, 0.90, 0.95, 1.05) → (Red, Amber, Green, Blue) and look it up with match_mode -1.

📦 Mini-project: Contract Lookup Card

Build a one-screen "Contract Card" sheet. Pick a ContractNumber from a data validation dropdown, and XLOOKUP fills in Vendor, ContractType, PoP dates, CeilingValue, ObligatedToDate, % obligated, and days remaining in PoP, with conditional formatting when PoP ends within 90 days. Data: contracts.