To match Myntra settlements to your bank statement in Excel, put the settlement report and bank statement on separate sheets, clean dates and amounts, and match each settlement's net payable to a bank credit using the payment reference, then amount and date window. Calculate the difference, mark each line as matched, difference or unmatched, and log exceptions with an owner and follow-up date.
Why settlements and bank credits rarely match one to one
The Myntra settlement report shows what the marketplace calculated as payable to you after sales, returns, commissions, fees and other adjustments. Your bank statement shows only what actually arrived. The two can differ for ordinary reasons:
- Timing: the credit lands on a later date than the settlement date.
- Batching: one bank credit may cover more than one settlement line, or one settlement may arrive in parts. Confirm how this works for your account from your reports.
- Deductions and adjustments: amounts withheld or adjusted before payout.
- Narration formats: banks shorten or reformat reference text.
Decide your matching level first. For bank matching, work at settlement level: one settlement total against one or more credits. Order-level checks against sales and returns are a separate exercise.
Prepare both files before you match
Most matching errors come from untidy data, not from formulas. Spend ten minutes on preparation.
- Keep raw copies. Paste the Myntra Excel report and the bank statement onto separate sheets and never edit the originals.
- Identify your key columns. In the settlement sheet, look for the settlement date, a payment or settlement reference, and the net payable amount. In the bank sheet, you need the credit date, narration, reference if present, and credit amount. Column names vary, so check your own report.
- Fix data types. Convert text-formatted amounts to numbers, remove commas stored as text, and make all dates real Excel dates.
- Normalise references. Use TRIM and UPPER in a helper column so spaces and case do not break lookups.
- Filter bank credits. Keep only credits relevant to the marketplace, and remove debits.
Step-by-step matching in Excel
- Add helper columns on the settlement sheet: Clean Reference, Bank Credit Found, Difference, Days to Credit and Status.
- Match on reference first. If the bank narration contains the payment reference, use XLOOKUP (Excel 2021 or 365) or INDEX with MATCH on older versions. When the reference is buried in narration text, a wildcard lookup or a helper column that extracts the reference works.
- Fall back to amount and date. Where there is no reference, use SUMIFS on the bank sheet with the exact amount and a date window, for example credits within a few days after the settlement date. Choose a window that suits your own payout pattern.
- Calculate the difference: settlement net payable minus bank credit found.
- Set a status using an IF formula: Matched if the absolute difference is within a small tolerance such as ₹1 (illustrative), Difference if a credit exists but the amount is off, and Unmatched if no credit is found.
- Reverse-check. Run the same logic from the bank sheet to settlements, so credits with no settlement behind them also surface.
- Total it. Compare total settlements and total credits for the month, and confirm that matched plus differences plus unmatched equals the full list.
Common mismatches and what to check
| What you see | Likely cause | What to check |
|---|---|---|
| Credit lower than settlement | Deduction or adjustment applied at payout | Settlement report deduction columns and your seller agreement |
| No credit found | Payout delayed, or credit under another account | All bank accounts, and the expected payout timeline for your account |
| One credit, many settlements | Combined payout | Group settlements by date and sum before comparing |
| Credit with no settlement | Prior-period payout or other adjustment | Earlier months and any manual notes |
| Same amount appears twice | Duplicate rows in an export | Remove duplicates by reference before matching |
Where TCS, TDS or GST-related deductions appear, they should be visible in your settlement report. Confirm the current rates and treatment from your agreement, the marketplace's published terms and your tax adviser. Do not hardcode rates into your sheet.
Keep an exceptions log and a monthly summary
Matching is only useful if the leftovers get resolved. Create an Exceptions sheet with one row per Difference or Unmatched item and these columns: settlement reference, expected amount, received amount, gap, date raised, owner, status and next follow-up date.
Then build a monthly summary with a PivotTable or SUMIFS: total settled, total received, matched value, value under investigation and value unmatched older than your internal cut-off. Review ageing every week, not at month-end, so a short payout does not go cold before you raise it with the marketplace.
When Excel starts to strain
Excel works well for one marketplace and a few hundred settlement lines a month. It gets fragile with multiple bank accounts, part payments, frequent returns and several people editing the same file. At that point, a tool such as MarketplaceMetrics by Fundle can reconcile sales and settlements, check commissions and fees against agreed terms, and track returns and recovery cases. Myntra Excel reports are reviewed today, and other marketplaces are in scope discussion. Until then, the process above gives you a solid, auditable baseline.
Worked example
Illustrative example only. All figures are made up to show the method.
| Settlement ref | Net payable (₹) | Bank credit (₹) | Difference (₹) | Status |
|---|---|---|---|---|
| SET-A | 1,84,250 | 1,84,250 | 0 | Matched |
| SET-B | 92,400 | 90,150 | 2,250 | Difference: check deductions |
| SET-C | 47,800 | 0 | 47,800 | Unmatched: check delay |
SET-A closes with no action. SET-B goes to the Exceptions sheet, where you compare the settlement report's deduction columns with the ₹2,250 gap. SET-C stays open with a follow-up date, and if no credit appears within your internal cut-off, you raise it with the marketplace.
Questions and answers
Which Excel function is best for matching Myntra settlements to bank credits?
XLOOKUP is the simplest on Excel 2021 or 365 because it returns a clear not-found value. On older versions, use INDEX with MATCH. For grouped credits or amount-and-date matching, SUMIFS is usually more reliable than a lookup.
What if the bank narration does not contain the settlement reference?
Match on exact amount within a date window using SUMIFS, then review the results manually. Where several settlements have identical amounts, add the credit date or account as extra criteria, and keep those lines flagged until you confirm them.
How do I handle one bank credit covering several settlements?
Group the settlement lines by settlement date or batch, sum their net payable amounts, and compare that total with the single credit. Record the grouping on your sheet so a reviewer can see which settlements were combined.
How often should I run this matching?
Weekly is practical for most brands, with a final pass at month-end. Frequent checks help you notice delayed or short payouts while the details are fresh and while any dispute window in your agreement is still open.