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

Download the AtlasParts dataset and the Episode 3 starter / completed workbooks — follow along in your own copy of Excel: github.com/GoCelesteAI/excel-powerbi-for-finance.

We left Episode 2 with the file readable. Twenty-two thousand rows of GL journal lines, every column understood, every journal entry balanced. But that file is still just rows. No CFO has ever asked for “the journal lines for fiscal 2025.” They ask for a P&L by product category. By customer country. By region. They want a one-page report.

Three Excel functions 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. Once you have these three, you can take any GL extract and turn it into a CFO-ready summary in an afternoon.

This episode walks all three on the AtlasParts data — building, by the end, a one-page P&L of revenue by category and customer country. We’ll also surface a problem the lookup can’t fix on its own — the customer master has the same country spelled three different ways — and that becomes the motivation for Episode 5.

The first formula: SUMIFS

You have 22,028 rows. You want one number: total revenue from engine parts in fiscal 2025.

=SUMIFS(gl_journal[credit], gl_journal[account_id], 4020)

That’s it. SUMIFS sums the credit column wherever the matching row’s account_id equals 4020 — the chart-of-accounts code for Sales – Engine. Run it across all 22K rows and you get the total credit posted to engine sales for the year.

The argument order matters. The first argument is what you’re summing — the column whose values get added up. Every argument after that is a (criteria_range, criteria) pair. You can chain as many as you need:

=SUMIFS(gl_journal[credit],
        gl_journal[account_id], 4020,
        gl_journal[posting_date], ">=2025-10-01",
        gl_journal[posting_date], "<=2025-12-31")

That gives you Q4 revenue from engine parts, in one cell. The criteria use Excel’s text-based comparison operators — ">=", "<=", "<>", and so on — wrapped in quotes. Numbers can go bare (4020), but anything with an operator must be a string.

A tip that bites every analyst eventually: SUMIFS treats blank cells as zero. If you’re using it on data where the criteria column might be blank — say, cost_center is missing on some rows — be aware that those rows will match "" (empty string) but not, say, ">0". When in doubt, filter the data first.

Building the revenue panel

With SUMIFS in hand, the revenue panel of a P&L writes itself. The chart of accounts has six revenue sub-accounts:

Account	Category
4010	Brakes
4020	Engine
4030	Electrical
4040	Suspension
4050	Body
4060	Accessories

For each, one SUMIFS:

Brakes:       =SUMIFS(gl_journal[credit], gl_journal[account_id], 4010)
Engine:       =SUMIFS(gl_journal[credit], gl_journal[account_id], 4020)
Electrical:   =SUMIFS(gl_journal[credit], gl_journal[account_id], 4030)
...

A six-row table. Total revenue is the sum of those six. There’s your top line.

You can also do this with a single ranged criterion:

=SUMIFS(gl_journal[credit], gl_journal[account_id], ">=4010", gl_journal[account_id], "<=4060")

That collapses the six SUMIFS into one, summing all revenue accounts in a single shot. Useful for a grand total; less useful when you need the breakdown.

The second formula: XLOOKUP

So far so good — but every formula above operates on the GL alone. The GL doesn’t know about customers; it has a source-doc column with values like SO-01284 and a description like “Invoice to CUST-007”, but customer country, customer name, customer industry — those live in customers.csv, a separate file.

To answer “how much revenue from US customers” we have to join the GL to the customer master. Excel’s modern join function is XLOOKUP.

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

In English: “find this value in this column; when you find it, return the corresponding value from this other column. If you can’t find it, return this fallback.”

The simplest possible use: given a customer ID, return the country.

=XLOOKUP("CUST-007", customers[customer_id], customers[country])

That returns "US". Done.

The fourth argument is the safety net. Without it, an unmatched lookup returns #N/A, which propagates into every formula that touches the result. Always include a fallback unless you specifically want failures to be loud:

=XLOOKUP("CUST-999", customers[customer_id], customers[country], "Unknown")

That returns "Unknown" instead of #N/A.

XLOOKUP arrived in Excel for Microsoft 365 around 2020. If you’re on Excel 2019 or earlier, you’ll have to use VLOOKUP or INDEX/MATCH (more on the latter shortly). For everyone on a current version, XLOOKUP wins on every dimension: the syntax is sane, it doesn’t care about column order, and it handles errors cleanly.

Chained XLOOKUP — the GL-to-customer pipeline

The GL doesn’t have a customer_id column. It has a source_doc column (SO-01284), and the connection to a customer goes through sales_orders.csv, which maps order IDs to customer IDs. So the chain is:

GL.source_doc  →  sales_orders.order_id  →  sales_orders.customer_id  →  customers.country

Two XLOOKUPs nested:

=XLOOKUP(
  XLOOKUP([@source_doc], sales_orders[order_id], sales_orders[customer_id], ""),
  customers[customer_id],
  customers[country],
  ""
)

The inner XLOOKUP turns a sales-order ID into a customer ID. The outer XLOOKUP turns the customer ID into a country. Each falls back to "" on a miss — important, because not every GL row is a sale (opening balances, depreciation, payroll all have non-SO source-docs and won’t match). Those rows will get an empty country, which we just won’t include in the per-country breakdown.

Drop that formula in a new column on the GL — call it customer_country — and Excel computes country for all 22K rows, in one shot. Now every GL row knows its country.

Building the 2D P&L

With the helper column populated, building a 2D pivot — revenue by category and country — is just SUMIFS with two criteria:

US revenue from engine parts:
=SUMIFS(gl_journal[credit],
        gl_journal[account_id], 4020,
        gl_journal[customer_country], "US")

Lay this out as a table — six rows (one per category) by however many countries — and you’ve got the panel. Total revenue at the bottom right; row totals on the right; column totals at the bottom. That’s a real P&L panel. From 22K rows of journal lines.

If you set up the SUMIFS once with cell-relative references and copy it across the grid, Excel does the rest. One formula, written once, populated across the whole table.

When to reach for INDEX/MATCH

XLOOKUP supersedes VLOOKUP and HLOOKUP for most practical work. But there’s still one thing INDEX/MATCH does that XLOOKUP can’t, cleanly: a two-way lookup, where you find a value at the intersection of a specific row AND a specific column.

Suppose you have the 2D P&L table you just built. Row labels are categories. Column headers are countries. You want a single cell that says “what was revenue at category X for country Y?”

=INDEX(pl_matrix,
       MATCH("Engine", category_labels, 0),
       MATCH("US",     country_labels,  0))

INDEX takes a range and a row/column index and returns the cell at that position. MATCH finds the position of a value in a one-dimensional list. Combine them and you can read any cell from any 2D table by its label coordinates.

You can do this with XLOOKUP too — nested:

=XLOOKUP("US", country_labels, XLOOKUP("Engine", category_labels, pl_matrix))

Either works. INDEX/MATCH is older, more verbose, and still preferred by many veterans because the formula reads as “given these two labels, find the cell” — which is exactly what you mean. Use whichever you find clearer.

The real reason to learn INDEX/MATCH today is legacy spreadsheets. If you inherit a workbook built before 2020, every lookup will be VLOOKUP or INDEX/MATCH. You’ll need to read it, modify it, and not break it. Leave it as INDEX/MATCH. Don’t refactor everything to XLOOKUP just because you can — your colleagues’ Excel may not support it.

Where this breaks: messy customer master data

Now, a small problem. When you sum revenue by country, your P&L will show:

Country	Revenue
US	$4,210,000
USA	$1,850,000
United States of America	$620,000
u.s.	$310,000
Germany	$1,180,000
DE	$440,000

Same country, written four different ways in the customer master, producing four different rows in your P&L. The lookup did its job perfectly — it matched what was in the data. The problem is the data. Real ERP master files are full of this kind of dirt: case differences, abbreviations, alternate spellings, trailing whitespace.

You can patch around it with formulas — =UPPER(TRIM(customer_country)) plus a normalization lookup table — but at scale this gets fragile and slow. The right tool for systematic cleanup is Power Query, and that’s exactly what Episode 5 is about. For now, just notice that the lookup-and-sum machinery works; the data underneath needs a wash.

What you can do now

You can take any GL extract, join in the master data you need, and produce a multi-dimensional summary table — by category, by region, by customer, by month, in any combination — with three functions and a couple of helper columns. That’s the formula layer of Excel finance work, the bedrock under everything else.

It’s also the layer that doesn’t scale well. Once your formulas point at thousands of cells across a dozen sheets and the workbook starts taking a minute to open, you’ll feel the ceiling. That’s where pivot tables (Episode 4) and Power Query (Episode 5) come in — both built to do this kind of aggregation at scale, without the per-cell formula tax.

But pivot tables and Power Query are layered on top of the same conceptual operations you just learned. Filter, lookup, aggregate. Knowing how to do it formula-by-formula is what makes you fluent enough to read pivot results, debug Power Query, and write DAX in Power BI without it feeling like magic.

Coming up next

Episode 4: Pivot Tables. We’ve been building the P&L panel with SUMIFS. Now we’ll build the same panel with a pivot — drag, drop, done — and see why every finance team’s reporting workflow eventually moves there. We’ll also see the new gotchas pivots introduce: blank rows, refresh anxiety, and the ever-present risk of GETPIVOTDATA.

See you there.