UPI reconciliation excel becomes manageable when you compare two clean lists: the payments your customers were expected to make and the credits that actually reached your merchant account. This guide builds a repeatable workbook that matches UTRs and amounts, highlights duplicates, and gives you a review queue for anything that needs attention.
The spreadsheet method is useful for a small batch or a historical clean-up. If you verify many payments each month, PaymentSetu Business gives you up to 100,000 verified payments per 30 days, with direct settlement to your own merchant account, a unified dashboard and webhooks for automated order updates.
What you need: statement and order list
Before opening Excel, decide the period you are reconciling and preserve the original downloads. You need one source of truth for expected payments and one for incoming UPI credits.
Create a workbook with three sheets:
- Orders — one row for each invoice, order or booking that should be paid.
- Bank — the UPI credits exported from the connected merchant account or bank statement.
- Review — a filtered copy or summary of missing, mismatched and duplicate rows.
The Orders sheet should contain at least these columns:
| Column | Purpose | Example |
|---|---|---|
| Order ID | Internal reference | ORD-1042 |
| Invoice number | Customer-facing reference | INV-2026-1042 |
| Order date | Date the payment was expected | 23-Oct-2026 |
| Customer | Helpful review label | Asha Traders |
| Expected amount | Amount due, stored as a number | 2499.00 |
| UTR | UPI transaction reference | 412345678901 |
The Bank sheet should contain the transaction date, UTR, description or payer narration, and credit amount. For the formulas below, use the headings Txn date, UTR, Description and Credit, or change the table-column names in the formulas to match your workbook. If your download includes debit rows, keep them in the original sheet but use only credit rows for the matching table. Do not delete the original file; make a working copy first.
Step 1: Import both lists into Excel Tables
Why: Tables keep formulas and filters consistent when you add another day or another batch of transactions.
- Open a new workbook and paste the expected-payment list into Orders.
- Paste the statement data into Bank. Keep the source columns visible so you can trace a match later.
- Remove blank header rows, merged cells and totals from the middle of either list.
- Select the Orders range and press Ctrl+T. Confirm that My table has headers is selected.
- Repeat for the Bank range.
- In Table Design, rename the tables
tblOrdersandtblBank. - Add these columns to Orders after
UTR:Bank date,Bank amount,Bank description,Status, andDuplicate flag.
Success looks like: both sheets have filter arrows on every heading, and adding a row directly below a table extends the table automatically.
Step 2: Clean the data
Why: A UTR that looks identical can still fail to match if one cell contains a leading apostrophe, spaces or a different data type. Amounts imported as text also make comparisons unreliable.
Work through these checks on the working copy:
- Format
Expected amount,CreditorAmountas Number with two decimal places. If a value is left-aligned or has a warning icon, convert it to a number before matching. - Format UTR columns as Text before pasting long references. This preserves the value exactly instead of allowing Excel to reinterpret it.
- Remove accidental spaces from a helper column. For example, if
Bank[UTR]is in column B, use=TRIM(B2)in a temporary column, fill down, then paste the cleaned results as values over the working UTR column. - Make sure both UTR headings use the same spelling and the same cell format. Do not match a UTR from one period against a statement from a different period without recording that decision.
- Filter the Bank table to credits only. Keep refunds, reversals and debits visible in the original source, but do not count them as successful incoming payments.
- Check that dates are real Excel dates rather than text. Sorting the date column from oldest to newest is a quick test.
If your statement has a combined narration rather than a separate UTR column, first extract the reference into a dedicated UTR column. Do not try to match on payer name alone: names can repeat, be abbreviated or be different from the order customer name.
Success looks like: amounts sort numerically, dates sort chronologically, and every row you intend to match has a visible UTR in the same format.
Step 3: Match on UTR with XLOOKUP
Why: The UTR is the strongest shared identifier between an incoming UPI credit and an order. XLOOKUP searches one column and returns a value from the matching row, with an explicit result when no match exists.
In tblOrders, enter these formulas in the new columns. Excel will usually fill them down the whole table.
In Bank date:
=IF([@UTR]="","",XLOOKUP([@UTR],tblBank[UTR],tblBank[Txn date],""))
In Bank amount:
=IF([@UTR]="","",XLOOKUP([@UTR],tblBank[UTR],tblBank[Credit],""))
In Bank description:
=IF([@UTR]="","",XLOOKUP([@UTR],tblBank[UTR],tblBank[Description],""))
The fourth XLOOKUP argument is the value to return when the UTR is not found. That is preferable to leaving a workbook full of #N/A errors during review. Microsoft documents the full XLOOKUP syntax and its exact-match default in its official XLOOKUP reference.
If your Excel version does not have XLOOKUP, use this older-compatible formula when the Bank table columns are UTR in B, Description in C and Credit in D:
=IFERROR(VLOOKUP([@UTR],tblBank[[UTR]:[Credit]],3,FALSE),"")
The FALSE argument requests an exact match. Microsoft notes that XLOOKUP is not available in Excel 2016 or Excel 2019, so check the version before sharing a workbook that depends on it.
Success looks like: a known UTR returns the corresponding date, amount and description; an unknown UTR stays blank rather than producing a misleading match.
Step 4: Compare the expected and received amounts
Why: A matching UTR is not enough to close an invoice if the credited amount differs from the amount due.
In Status, use a small tolerance for paise-level decimal differences:
=IF([@UTR]="","Missing UTR",IF([@[Bank amount]]="","Not found",IF(ABS([@[Bank amount]]-[@[Expected amount]])<0.01,"Matched","Amount mismatch")))
This creates four useful outcomes:
| Status | What it means | Action |
|---|---|---|
| Matched | UTR exists and the amounts agree | Mark the order reconciled in your process |
| Missing UTR | The order row has no reference | Check the payment record or customer communication |
| Not found | The UTR is not in this Bank list | Check the date range, account and source export |
| Amount mismatch | The UTR exists but values differ | Compare invoice, credit and any adjustment record |
Use conditional formatting to make Matched green, Not found amber and Amount mismatch red. Select the Status column, choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains, and create one rule for each status.
Do not use a simple amount-only lookup as your final match. Two customers can owe the same amount, so an amount can support a UTR match but should not replace it.
Success looks like: the Status column tells you what to do next without opening every statement line, and the matched total can be compared with the expected total.
Step 5: Flag duplicates and unmatched bank credits
Why: Duplicate UTRs and statement rows can make totals look correct while one order is incorrectly closed or one credit is counted twice.
In Duplicate flag, enter:
=IF([@UTR]="","",IF(COUNTIF(tblOrders[UTR],[@UTR])>1,"Duplicate UTR",""))
Filter for Duplicate UTR. Review the original rows before deciding which entry is valid. Do not use Data > Remove Duplicates as the first step: Microsoft warns that removing duplicates permanently deletes the selected duplicate data. Use conditional formatting or a filter to review first, and keep your source copy.
To find credits in the Bank table that were not used by an order, add a Used by order column to tblBank:
=IF([@UTR]="","Missing UTR",IF(COUNTIF(tblOrders[UTR],[@UTR])=0,"Unmatched bank credit","Used"))
Investigate Unmatched bank credit rows. They may belong to an order outside the selected period, an order that was not exported, a refund or reversal workflow, or a payment that needs manual identification. Record the reason in a Review sheet instead of silently deleting the row.
For a second duplicate check, select the Bank UTR column and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. The Microsoft guide to finding and removing duplicates explains why highlighting is safer than immediately deleting records.
Success looks like: you have separate lists for matched orders, orders needing review, and bank credits not linked to an order. Your final incoming-credit total is not inflated by repeated rows.
Step 6: Review the reconciliation totals
Why: Row-level matches can still hide an incomplete date range or a wrong account, so finish with control totals.
At the top of the Review sheet, record:
| Control | Excel formula or check |
|---|---|
| Expected order count | =ROWS(tblOrders[Order ID]) |
| Matched order count | =COUNTIF(tblOrders[Status],"Matched") |
| Orders needing review | =COUNTIF(tblOrders[Status],"<>Matched") |
| Expected amount total | =SUM(tblOrders[Expected amount]) |
| Matched amount total | =SUMIFS(tblOrders[Expected amount],tblOrders[Status],"Matched") |
| Bank credit total | =SUM(tblBank[Credit]) |
Then check the filters and the source period. The expected total and bank total do not have to be identical when the statement includes unrelated credits or when the two lists cover different dates, but every difference should have a recorded explanation.
Save the workbook with the period in its name, such as upi-reconciliation-2026-10.xlsx. Keep the source exports with it and protect formula columns from accidental edits if other people will use the file.
Success looks like: another person can open the workbook, see the period and source files, understand the status rules, and reproduce the totals.
Skip the spreadsheet: automatic matching
Excel is a good audit and clean-up tool, but repeated manual imports create work every billing cycle. A payment-verification workflow can create an order-specific UPI request, match the incoming UTR and amount, and send the result to your order system through a webhook.
PaymentSetu is a verification service, not a payment gateway: the customer pays directly into your own Paytm Business or PhonePe Business merchant account. The service matches the payment to the order, while you can review or export verified payment records from a unified dashboard and connect your workflow through webhooks or the REST API.
For a high-volume operation, the PaymentSetu Business plan is ₹1,499 per 30 days and includes 100,000 credits; one credit represents one successful payment verified. It also includes dynamic QR, webhooks, API access, priority support and a dedicated account manager. See the registration page when you are ready to connect your workflow.
If you run a store, the WooCommerce plugin can update an order after verification. For hosting invoices, the WHMCS module can update the invoice after verification. In either case, keep the Excel reconciliation as a periodic audit or exception report rather than making it the place where every payment is manually identified.
Troubleshooting common reconciliation problems
XLOOKUP returns a blank for a visible UTR
Compare the two cells with =LEN(A2) and =LEN(B2) in temporary columns. A leading or trailing space, a hidden character or text-versus-number formatting can prevent an exact match. Clean both UTR columns consistently, then copy the cleaned values back as text.
The same UTR matches more than one row
Filter both tables for that UTR and inspect the source rows. A duplicate in the statement and a repeated UTR in Orders are different problems; keep both visible until you know which row represents the real payment. Never close both orders just because the lookup found a value.
The amount looks equal but Status says Amount mismatch
Check whether one amount is stored as text, whether one side includes a decimal fraction, or whether the invoice amount changed. Convert both columns to numbers, show two decimals, and compare the original statement with the invoice before changing the tolerance.
VLOOKUP gives the wrong result
Confirm that the lookup range starts with UTR and that the last argument is FALSE. VLOOKUP returns the first matching row, so duplicate UTRs must be flagged separately rather than hidden by the formula.
FAQ
What is the best key for UPI reconciliation in Excel?
Use the UTR as the primary key and the payment amount as a validation check. A payer name or amount alone is not sufficiently specific when several customers pay similar amounts.
Can I reconcile UPI payments without a UTR?
You can create a review list using date, amount and description, but do not mark a row as fully matched on those fields alone. Ask for the transaction reference or check the source payment record before closing the invoice.
Is XLOOKUP better than VLOOKUP for payment matching?
XLOOKUP is easier to read because it names the lookup and return columns and lets you define a not-found result. VLOOKUP remains useful on older Excel versions, but its lookup range must begin with the UTR column and its exact-match argument should be FALSE.
How often should a business reconcile UPI payments?
Reconcile frequently enough that an unmatched payment can still be traced to its order and source statement. Daily or per settlement period is practical for busy stores; lower-volume businesses can use a fixed weekly or monthly close with a documented date range.
How can I reduce manual UPI reconciliation work?
Use order-specific references, retain the UTR and amount in your records, and connect payment verification to your order or invoice system. PaymentSetu can match payments sent to your own connected merchant account and notify your application through webhooks, reducing the rows that need manual review.
DONE reconcile-upi-payments-in-excel words=1874