Retrieve a payment with a fallback
Return the paid amount for INV-3, or the text Missing if there is no payment.
Formula: =XLOOKUP("INV-3",D2:D5,E2:E5,"Missing") → 700
Hands-on Excel exercises
Advanced Excel Skills Test Practice
Practice advanced Excel skills by reconciling invoices against payments: missing-item checks, XLOOKUP fallbacks, and control totals, with answers.
Advanced tests stop asking whether you can write a formula and start asking whether you can trust one. Typical tasks give you two lists that should agree, such as invoices and payments, and ask you to find what is missing, what differs, and how large the gap is. A candidate who produces a number is average. One who produces a number, a control total, and a note on the exception stands out.
The live sheet holds five invoices on the left and four payments on the right. Your task is to count the invoices that never appear in the payments list. That needs COUNTIF of each invoice against the payments list: a count of zero means the invoice has no partner, and SUMPRODUCT adds those up. The worked answers then retrieve a payment with a clear not-found message, show the message for the missing invoice, and compute the overall gap between billed and paid.
Treat each answer as a reviewable claim. Say what you matched on, what happens when a key is absent, and how you know the totals tie out. A shorter transparent formula is almost always better than a clever one. When you finish, apply the same approach to a role page that matches your target job, such as financial analysts or data analysts. The robust, explainable method transfers to every one of them.
Your turn
Your turn: in G2, count the invoices in A2:A6 that do not appear in the payments list D2:D5.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Invoice | Billed | Paid invoice | Paid amount | |||
| 2 | INV-1 | 500 | INV-1 | 500 | |||
| 3 | INV-2 | 300 | INV-3 | 700 | |||
| 4 | INV-3 | 750 | INV-4 | 120 | |||
| 5 | INV-4 | 120 | INV-5 | 980 | |||
| 6 | INV-5 | 980 | |||||
| 7 |
Not yet solved.
Answer: =SUMPRODUCT((COUNTIF(D2:D5,A2:A6)=0)*1)
Practice under a time limit
Try the free Sample mock exam with 4 tasks in 8 minutes, or browse the level and interview mock exams included with Pro + Exams.
Related exercises
Open a task in its lesson.
- In G2:G5, look up the price, and show 0 for an unknown SKU.
- In G2:G4, find the employee ID for each name. The ID is to the left of the name, so VLOOKUP will not help.
- Fix D5: the number of units of juice sold.
- In F6, find the weakest result with the SMALL function.
- In I15, use a formula to find the name of the top destination in the first PivotTable.
- In F6, calculate the month's share of the half-year revenue and turn on the "%" format.
Worked answers
Try the spreadsheet first, then compare your formula and result.
Show the missing payment
Return the paid amount for INV-2, or the text Missing if there is no payment.
Formula: =XLOOKUP("INV-2",D2:D5,E2:E5,"Missing") → Missing
Compute the control gap
What is the difference between the total billed and the total paid?
Formula: =SUM(B2:B6)-SUM(E2:E5) → 350
Frequently asked questions
Why compare COUNTIF with zero?
COUNTIF of an invoice in the payments list returns 0 when it is absent. Testing for zero gives a TRUE or FALSE per invoice that SUMPRODUCT can add up, without any error values.
How is this different from VLOOKUP?
Here you test a whole list at once and count the failures. A lookup retrieves one value; a reconciliation asks which items have no partner.
What is a control total?
A figure that must tie out, such as billed minus paid. If it does not match your detail, a row is missing or duplicated.
Should I always avoid nested formulas?
Not always, but be able to explain each layer in one sentence. If you cannot, split the formula into helper cells.