XLOOKUP for Accountants

The syntax

=XLOOKUP(what you are looking for, where to look for it, what to return)

Three arguments. Compare that to VLOOKUP, which needs a column number you have to count and which breaks when someone inserts a column.

The three VLOOKUP problems it fixes

It looks left. VLOOKUP can only return a value to the right of the lookup column. XLOOKUP does not care about position.

It survives inserted columns. No column index number to become wrong.

It defaults to exact match. VLOOKUP defaults to approximate, which is the source of a great many quiet errors in accounting spreadsheets.

The fourth argument, which is the useful one

=XLOOKUP(A2, Suppliers!A:A, Suppliers!C:C, "NOT FOUND")

That fourth argument is what to show when there is no match.

In reconciliation work this is the whole point. You want the non-matches visible, not hidden behind #N/A. Filter on "NOT FOUND" and you have your exceptions list.

Three uses in accounting work

1. Matching a bank statement to your ledger.

Look up each bank reference in your ledger export. Anything returning NOT FOUND is either unposted or a difference to investigate.

2. Adding supplier or customer detail to a transaction list.

Pull the supplier category, payment terms or TRN onto a payment listing so you can group and analyse it.

3. Comparing two periods.

Look up this year's account codes against last year's to find accounts that appeared, disappeared or moved.

Matching on two conditions

Where one column is not unique, concatenate.

=XLOOKUP(A2&B2, Sheet2!A:A&Sheet2!B:B, Sheet2!C:C, "NOT FOUND")

Invoice number and supplier together, for example, where invoice numbers repeat across suppliers.

The habit that matters more than the function

Never leave an error visible in a working file without knowing what it means.

An #N/A that someone wraps in IFERROR to make the sheet look clean has hidden a difference. In a reconciliation, the differences are the output.

If you do not have XLOOKUP

It is in current versions of Excel and Microsoft 365. Older versions do not have it, and INDEX with MATCH does the same job with more typing.

The wider point

If you are doing large reconciliations in Excel every month, the question is why the data needs reconciling by hand at all.

Usually the answer is that two systems are not connected, or the bookkeeping is not current. Both are worth fixing at the source rather than automating the workaround.

Where we fit

We do this work at scale as part of audit and bookkeeping. If you are spending days a month on manual matching, that is usually a symptom rather than a task.