Lookups for Finance: SUMIFS, XLOOKUP, INDEX/MATCH | Excel & Power BI S1 Ep3

📂 Download the AtlasParts dataset and the Episode 3 starter / completed workbooks: https://github.com/GoCelesteAI/excel-powerbi-for-finance Episode 3 of *Excel & Power BI for Finance*. We left Episode 2 with the file readable — twenty-two thousand GL journal lines, every column understood, every entry balanced. But that file is still just rows. No CFO ever asked for "the journal lines for fiscal 2025." They ask for a P&L by category. By customer country. By region. They want a one-page report. This episode walks the three Excel functions that get you from rows to one page. SUMIFS does the aggregation. XLOOKUP does the joining. INDEX/MATCH is the fallback for older Excel and the only good answer for two-way lookups. By the end, we'll have a one-page P&L of revenue by category and country — built on AtlasParts' twenty-two thousand row GL extract. We'll also surface a problem the lookup can't fix on its own — the customer master has the same country spelled four different ways — and that becomes the motivation for Episode 5: Power Query. What You'll Learn: - The SUMIFS argument structure: sum range first, then criteria range / criteria pairs. How to add multiple criteria to a single SUMIFS for filtered totals (Q4 engine revenue, US engine revenue, etc.) - The chart-of-accounts trick: revenue accounts (4xxx) are split by category, so a SUMIFS by account_id gives you revenue per product category in one cell each - XLOOKUP — Excel's modern join function. Find a value, return the matching value from another column, with a fallback for unmatched lookups. Why the 4th-argument fallback is non-negotiable - Chained XLOOKUP — when the join goes through an intermediate table. GL.source_doc → sales_orders.customer_id → customers.country in one nested formula - Adding a helper column to the GL: 22,028 rows of customer_country populated from one formula - Building a 2D P&L: rows = categories, columns = countries. Mixed cell references ($A2 / B$1) so one formula populates the whole grid by drag-fill - INDEX/MATCH — the older sibling. Why it still has its place: legacy spreadsheets that pre-date XLOOKUP, and the cleanest two-way lookup syntax - The cliffhanger: customer master files are dirty in real life. "US", "USA", "U.S.", "United States of America" all show up in the same column and produce duplicate rows in your P&L. Episode 5 is where we clean that up Timestamps: 0:00 - Intro — Lookups for Finance 0:28 - What's in this episode 1:00 - The state we're in — 22,000 rows, no P&L 1:48 - The first formula — SUMIFS basics 2:58 - Building the revenue panel 3:53 - The second formula — XLOOKUP 4:55 - Chained XLOOKUP — GL → sales_orders → customers 6:05 - The 2D P&L — one formula, drag right, drag down 7:05 - The older sibling — INDEX/MATCH 8:05 - One small problem — dirty customer master data 8:55 - Recap — three takeaways 9:30 - Up next · Episode 4 Key Takeaways: 1. SUMIFS aggregates. Sum range first, then criteria pairs. Numbers go bare; anything with an operator wraps in quotes. Add as many criteria pairs as you need. 2. XLOOKUP joins. Always include the 4th-argument fallback — without it, an unmatched lookup poisons every formula downstream with #N/A. 3. INDEX/MATCH still has its place. Pre-2020 spreadsheets need to stay readable, and two-way lookups (cell at the intersection of row X and column Y) are cleanest written as INDEX(matrix, MATCH, MATCH). 4. The unit of meaning shifts from rows to grid. Set up a SUMIFS once with mixed absolute references ($A2, B$1) and Excel populates the entire grid by drag-fill. That's how a one-page P&L gets built. 5. The lookup is correct, but the data is dirty. Real customer master files have the same country spelled four different ways. The aggregation works mechanically; the data quality is an Episode 5 problem. Next episode: *Pivot Tables*. We build the same panel by drag and drop, and meet GETPIVOTDATA. Taught by CelesteAI. #exceltutorial #powerbi #financetraining #financeprofessionals #sumifs #xlookup #indexmatch #generalledger #financialreporting #excelforaccountants #cfo #excelandpowerbi #excelforfinance #financialanalysis #atlasparts