Tip of the week: XLOOKUP for lead schedules
When you build a lead schedule, you usually pull balances from the trial balance by account code. In Excel 2021 and Microsoft 365, XLOOKUP does this cleanly:
=XLOOKUP(A2, TB!A:A, TB!C:C, "Not found")- Its default match is exact, so it will not quietly pick a neighbouring account.
- The fourth argument shows "Not found" instead of an error, so a missing account stands out.
- The return column is chosen directly, so inserting a column in the trial balance does not break the formula.
Whatever the formula, finish with a check: the lead schedule total should agree to the trial balance.
Common mistake: VLOOKUP without FALSE
=VLOOKUP(A2, TB!A:C, 3)When the last argument is left out, VLOOKUP's default is approximate match. If the account codes are not sorted, or the code you are looking for is missing, it can return the wrong row without any error — and the number looks perfectly reasonable.
The fix is one word:
=VLOOKUP(A2, TB!A:C, 3, FALSE)Or use XLOOKUP, where exact match is already the default.
Quick quiz
Under IFRS 9's simplified approach, the loss allowance on trade receivables is measured at?
Answer: Lifetime expected credit losses.
In a bank reconciliation, a cheque issued but not yet presented is?
Answer: Deducted from the balance per bank statement.
What is XLOOKUP's default match mode?
Answer: Exact match.
A tick mark without a legend is a puzzle for the next reviewer.
General guidance for professional development. It does not describe the terms of any specific role or firm.