XLOOKUP does what VLOOKUP and INDEX/MATCH did, with fewer ways to go wrong.
XLOOKUP finds a value in one range and returns the matching value from another: =XLOOKUP(lookup_value, lookup_array, return_array, if_not_found, match_mode, search_mode). Accountants use it to bring supplier names onto invoice lists, map accounts to reporting lines, find the latest exchange rate and match items across two reports. It is available in Microsoft 365 and Excel 2021 or later.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Invoice list with supplier ID in column B; supplier master in a table named Suppliers.
=XLOOKUP(B2,Suppliers[ID],Suppliers[Name],"Not in master")
"Not in master" is far more useful than #N/A: it tells you an invoice has been entered against a supplier ID that does not exist.
Trial balance account in column A; mapping table Map with Account and Line.
=XLOOKUP(A2,Map[Account],Map[Line],"UNMAPPED")
Then add a check that no account is unmapped before the reporting pack is built:
=COUNTIF(D:D,"UNMAPPED")
It should be zero.
To see whether each bank statement amount and reference appears in the ledger export, look up a combined key. In both lists, build a key in a helper column:
=TRIM(C2)&"|"&TEXT(D2,"0.00")
Then in the bank list:
=XLOOKUP(E2,Ledger[Key],Ledger[Date],"No match")
Items showing "No match" are your starting list of reconciling items. Remember that a simple lookup finds the first match only, so two identical amounts can both point at the same ledger line. For reconciliations, count matches too:
=COUNTIF(Ledger[Key],E2)
and investigate any key that appears more than once. The transaction reconciliation guide walks through a full bank-to-ledger match.
Rates table FX sorted by date ascending, with Date and Rate columns. To find the rate on the transaction date, or the most recent one before it:
=XLOOKUP(A2,FX[Date],FX[Rate],"No rate",-1)
Match mode -1 returns an exact match, or the next smaller date if there is no rate for that day, such as at weekends.
Payments list sorted by date. To return the last payment amount for a supplier, search from the bottom up:
=XLOOKUP(H2,Payments[Supplier],Payments[Amount],"None",0,-1)
Search mode -1 searches last to first, so it returns the latest matching row as long as the list is in date order.
To find an invoice amount by supplier and invoice number together:
=XLOOKUP(1,(Inv[Supplier]=H2)*(Inv[Number]=I2),Inv[Amount],"Not found")
Each condition returns TRUE or FALSE; multiplying them gives 1 only where both are true. Use table columns or fixed ranges rather than whole columns, as this form is slower.
To pull a budget figure by account (rows) and month (columns):
=XLOOKUP(H2,Budget[Account],XLOOKUP(I1,Budget[#Headers],Budget))
The inner XLOOKUP returns the whole month column; the outer one picks the account's row from it.
XLOOKUP works the same in UK and US versions of Excel. Function names are English in both. Regional settings can change the argument separator (a comma or a semicolon) and the date format, so check both if a formula copied from elsewhere does not work.
AI software that matches your bank statement to your ledger on your own computer and builds the reconciliation statement. $1,997 once-off; see it on the built-in example first.
The Balance Sheet Reconciliation Library covers the balance sheet with nine working papers: a known error blocks sign-off, every supported balance needs an evidence reference and every aged item needs an owner and a next action. Excel, no macros, one-time price.
For most finance work, yes. It can look left, defaults to exact match, has built-in not-found handling and does not break when columns are inserted. VLOOKUP still works in older versions.
Microsoft 365 and Excel 2021 or later, including Excel for the web. Older versions show #NAME?.
Use 1 as the lookup value and multiply the conditions as the lookup array, for example (Supplier=H2)*(Number=I2), returning the column you need.
Last updated 11 November 2026. General guidance for finance teams; check it against your own policies, systems and local rules.