Spreadsheets

Top 5 biggest expenses with LARGE and INDEX MATCH

List your five biggest expenses in Excel with LARGE and INDEX MATCH, including a trick for equal amounts, plus the SORTBY and TAKE shortcut.

Editorial illustration for Top 5 biggest expenses with LARGE and INDEX MATCH

To list your five biggest expenses in Excel, use =LARGE(Data[Amount], 1) for the first amount, 2 for the second, and so on, then fetch the merchant with =INDEX(Data[Merchant], MATCH(amount, Data[Amount], 0)). If two amounts are equal, MATCH returns the first one twice, so add a tiny tie-breaker column. Excel 365 users can use SORTBY and TAKE instead.

The basic version

Assume your transactions are in a Table named Data with Date, Merchant, Category and Amount, and all amounts are positive expenses. Put ranks 1 to 5 in E2:E6. Then:

  • F2, amount: =LARGE(Data[Amount], E2)
  • G2, merchant: =INDEX(Data[Merchant], MATCH(F2, Data[Amount], 0))
  • H2, date: =INDEX(Data[Date], MATCH(F2, Data[Amount], 0))

Fill down to row 6. LARGE takes the array first and the rank second; the last argument 0 in MATCH asks for an exact match. Format the date column as a date, since INDEX returns the underlying serial number.

If you ever have fewer than five transactions in a period, LARGE returns a #NUM! error. Wrap each formula in IFERROR, for example =IFERROR(LARGE(Data[Amount], E2), ""), so the unused rows stay blank instead of showing errors on a sheet you may share.

What goes wrong when two amounts are equal?

Say you paid ₹4,999 to two different sellers on two days. LARGE correctly returns 4,999 for both rank 1 and rank 2. But MATCH looks for the first 4,999 in the column and finds the same row both times, so you see one merchant twice and the other never appears.

The tie-breaker trick

Add a column called Key to the Table with this formula:

=[@Amount]+ROW()/10^8

Each row gets a tiny, unique fraction of a rupee on top of its amount. Rows 14 and 52 with 4,999 become 4,999.00000014 and 4,999.00000052, so they now differ. Then rebuild the list on Key:

  1. H2: =LARGE(Data[Key], E2)
  2. Merchant: =INDEX(Data[Merchant], MATCH(H2, Data[Key], 0))
  3. Date: =INDEX(Data[Date], MATCH(H2, Data[Key], 0))
  4. Amount: =INDEX(Data[Amount], MATCH(H2, Data[Key], 0))

Read the real amount from the Amount column, not the Key, so the extra decimals never show. The fraction is far too small to change the ranking between genuinely different amounts.

RankAmount (₹)Merchant (example)Why it needs the Key
14,999Seller AEqual to rank 2
24,999Seller BEqual to rank 1
33,250Electronics shopUnique
42,800Insurance premiumUnique
52,150Appliance repairUnique

Is there a shorter way in Excel 365?

Yes. SORTBY needs Excel 2021 or Microsoft 365, and TAKE needs Microsoft 365. In one cell:

=TAKE(SORTBY(Data[[Date]:[Amount]], Data[Amount], -1), 5)

It sorts the Date-to-Amount columns by Amount from largest to smallest and keeps the first five rows, and equal amounts need no special handling because whole rows travel together. The result spills across a block of cells, so keep that area empty. To limit it to one month, wrap the Data table in FILTER with Date criteria.

To see the top five for one month only, use AGGREGATE, which handles arrays without Ctrl+Shift+Enter: =AGGREGATE(14, 6, Data[Key]/((Data[Date]>=$F$1)*(Data[Date]<EDATE($F$1,1))), E2). Function 14 is LARGE, and option 6 ignores the divide-by-zero errors that appear for dates outside the month. Then use the same INDEX and MATCH lookups on the Key column as before.

Why bother with a top-five list?

A short list of large items shows where the money went without scrolling hundreds of rows. It is also where one-offs such as an annual premium or a festival purchase surface. Look at it monthly and ask which of the five were planned.

If your biggest expenses are scattered across several bank and card statements, SpendixAI lets you pick the account before importing, so each transaction keeps its account, and its reports can then show larger spends. For a single account, the formulas above are enough.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports