Content authored by practitioners with experience at Amazon India, Intuit QuickBooks, and the Tata Group. Meet the team →
A junior accountant needs to pull bank amounts next to a payment list to compare them and is unsure which lookup function to use.
Use XLOOKUP where available, INDEX-MATCH otherwise, and VLOOKUP only with an exact-match argument and the key in the first column; clean keys before looking up and use a combined key when one column is not unique.
Two lists with a shared key such as the UTR, a lookup column on the first list, a difference column and a fallback value for missing keys.
Each line on the first list shows the matched amount from the second list, its difference, or a clear not-found flag.
Use XLOOKUP if your Excel has it, which means Microsoft 365 or Excel 2021 and later. Use INDEX-MATCH if it does not. Use VLOOKUP only when the key sits in the first column of the range you look up in, and always with exact match. All three do the same job here: fetch the amount from list B and place it next to list A so you can compare.
How do the three compare?
| VLOOKUP | INDEX-MATCH | XLOOKUP | |
|---|---|---|---|
| Excel versions | All | All | Microsoft 365, Excel 2021 and later |
| Key position | Must be the first column of the range | Any column | Any column |
| Look left of the key | No | Yes | Yes |
| Default match type | Approximate, unless you add FALSE | MATCH defaults to approximate, unless you add 0 | Exact |
| Key missing | #N/A | #N/A | #N/A, or your own text if you give the fourth argument |
| Breaks if a column is inserted | Yes, the column number is fixed | No | No |
The approximate default is the trap. A VLOOKUP without FALSE can return a nearby amount instead of #N/A, and the mistake looks like a match.
Worked example: six payments matched by UTR
Your payment register has six vendor payments in September, totalling ₹7,42,500. The bank extract is on a sheet called Bank, with UTR in column A, date in B and amount in C.
Register sheet, UTR in column B, amount in column D:
| Vendor | UTR | Date | Amount | Bank amount (lookup) |
|---|---|---|---|---|
| Arun Brothers | 426011223344 | 2 Sep | ₹1,25,000 | ₹1,25,000 |
| Kiran Steel | 426055667788 | 5 Sep | ₹2,10,000 | ₹2,10,000 |
| Sai Packaging | 426099001122 | 9 Sep | ₹48,500 | Not found |
| Metro Logistics | 426133445566 | 12 Sep | ₹96,000 | ₹96,000 |
| Delta Chemicals | 426177889900 | 18 Sep | ₹1,63,000 | ₹1,63,000 |
| Vyas Traders | 426211335577 | 24 Sep | ₹1,00,000 | ₹1,00,000 |
Check the total: ₹1,25,000 plus ₹2,10,000 is ₹3,35,000. Add ₹48,500 for ₹3,83,500. Add ₹96,000 for ₹4,79,500. Add ₹1,63,000 for ₹6,42,500. Add ₹1,00,000 for ₹7,42,500.
The three formulas, in E2:
VLOOKUP (UTR is in Bank column A, the first column, so this works):
=IFNA(VLOOKUP(B2, Bank!$A:$C, 3, FALSE), "Not found")INDEX-MATCH:
=IFNA(INDEX(Bank!$C:$C, MATCH(B2, Bank!$A:$A, 0)), "Not found")XLOOKUP:
=XLOOKUP(B2, Bank!$A:$A, Bank!$C:$C, "Not found")All three return the same results. Five payments find their amount; the ₹48,500 payment to Sai Packaging returns “Not found”. Matched value is ₹6,94,000; ₹48,500 needs checking. IFNA works in Excel 2013 and later; in Excel 2010 use IFERROR instead.
When the accountant checks, the bank shows a ₹48,500 debit on 9 September with UTR 426099001123: the last digit was typed wrong in the register.
Why does a lookup return #N/A or the wrong row?
| Problem | What happens | Fix |
|---|---|---|
| Approximate match | VLOOKUP without FALSE, or MATCH without 0, returns a near value | Always add FALSE or 0 |
| Number versus text | The 12-digit UTR is a number on one sheet and text on the other | Convert both: =TRIM(TEXT(B2,“0”)) or wrap in TRIM if already text |
| Trailing spaces | ”426011223344 ” does not equal “426011223344” | TRIM both sides into a helper column |
| Long references | Excel keeps only 15 significant digits in a number, so a longer all-digit reference loses its last digits | Import or format the column as Text before pasting |
| Duplicate keys | All three return the first match only | Count keys with COUNTIF first; treat duplicates separately |
How do I match on amount plus date plus narration together?
When there is no UTR in one list, combine columns into one key.
Helper key (all versions): in both lists, add
=TEXT(C2,"0.00")&"|"&TEXT(B2,"dd-mm-yyyy")and look up that key instead of the UTR.
XLOOKUP with two conditions (Microsoft 365, Excel 2021 and later):
=XLOOKUP(1, (Bank!$C$2:$C$500=D2)*(Bank!$B$2:$B$500=C2), Bank!$A$2:$A$500, "Not found")This returns the bank UTR for the line with the same amount and the same date. Use fixed ranges rather than whole columns here to keep it quick. To add a narration test, multiply in a third condition such as ISNUMBER(SEARCH(“SAI”,Bank!$D$2:$D$500)).
Two payments of the same amount on the same day will still collide. That is the point where one key is not enough.
What to read next
Next, read how to compare two Excel files with Power Query Merge. To pull the UTR out of a bank narration first, see the bank narration parsing workbook, and for when formulas stop scaling, Excel versus Python versus reconciliation software.
TransactIG matches bank statement lines to your books, with an exception queue for what is left and audit-ready exports; see bank reconciliation software for India.