Formulas With Explanations: XLOOKUP, SUMIFS, Nested IF

Formulas with explanations: XLOOKUP, SUMIFS and nested IF

Goal: get three everyday formulas from Claude, understand every argument, and prove each one works on real rows before you trust it.

Plan check: everything in this lesson works on Claude's free plan (chat, attaching a file, and Claude running a short analysis on a demo file) plus free Google Sheets or any recent Excel. The free plan has lower usage limits. The Claude for Excel add-in (works inside Excel) needs a paid Claude plan (Pro and up, about $20/month) — it is optional here.

Files: invoices-h1-2026.csv and clients.csv. Open both in one workbook as two sheets named invoices and clients. All names and numbers in the demo files are fictional.

XLOOKUP exists in Microsoft 365, Excel 2021 and later, and Google Sheets. On Excel 2016/2019 ask Claude for the INDEX/MATCH version instead.

Step 1 — XLOOKUP: bring the client name into each invoice (4 min)

I have two sheets.
Sheet "invoices": columns A–H = invoice_no, invoice_date, client_id, description, amount_net, vat_18, amount_gross, status. Data starts in row 2.
Sheet "clients": columns A–D = client_id, client_name, city, payment_terms_days.
Write an XLOOKUP for invoices!I2 that returns client_name for the client_id in C2, and shows "not found" if the id is missing.
Explain each argument in one line. Then give me the INDEX/MATCH version for older Excel.

Paste the formula in I2, fill down. Check: pick two invoices and look the client up by eye in clients.

Step 2 — SUMIFS: total paid per client (4 min)

On a new sheet I have client_id values in A2:A9.
Write a SUMIFS for B2 that sums invoices!E:E (amount_net) where invoices!C:C equals A2 AND invoices!H:H equals "paid".
Explain each pair of arguments. Then tell me one quick way to check the result.

Check: the sum of column B must equal the total of all paid invoices. In the demo file that is 86,250 net. All invoices together are 113,250, of which 27,000 are still open. If your numbers differ, find out why before moving on.

Step 3 — Nested IF: label overdue invoices (4 min)

In invoices!J2 I want a status label:
- if status is "paid" → "paid"
- if status is "open" and more than payment_terms_days have passed since invoice_date (use a fixed report date of 2026-06-30 in cell L1) → "overdue"
- otherwise → "open"
payment_terms_days comes from the clients sheet by client_id.
Write it with nested IF, then with IFS, and explain the order of the conditions.

Check: test it on one paid invoice, one open invoice from late June (should be "open") and change L1 to a later date to watch it flip to "overdue".

Inherited a formula you don't understand?

Explain this formula step by step, as if to a new colleague. Say what happens if a value is missing:
=PASTE_THE_FORMULA_HERE

Verification checklist

• Two XLOOKUP rows checked by eye
• SUMIFS column totals 86,250 (paid) and ties to the invoices sheet
• The overdue label flips when you change the report date
• You can say in one sentence what every argument does

Exercise (the editor below)

Build a formula card with three <article class="formula"> blocks, each with the formula in <code> and a one-line explanation in <p>. This is the cheat sheet you will reuse.