Spreadsheets

Find the top merchant in each category with MAXIFS and XLOOKUP

Find the top merchant in each spending category with a helper table, MAXIFS and XLOOKUP in Excel, with INDEX and MATCH for Google Sheets.

Editorial illustration for Find the top merchant in each category with MAXIFS and XLOOKUP

To find which merchant takes the most spend in each category, first total the spend for every category and merchant pair, then use MAXIFS to find the largest total per category and a lookup to name the merchant that owns it. Excel 365 or 2021 handles it with XLOOKUP; Google Sheets can use INDEX and MATCH.

How do I build the category and merchant totals?

Your Data sheet has category in B, merchant in C and amount in D, rows 2 to 500. On a Work sheet, list each unique pair. In Excel 365 or 2021, =UNIQUE(Data!$B$2:$C$500) in H2 spills two columns: category in H, merchant in I. In J2, add the total for that pair and fill down:

  • =SUMIFS(Data!$D$2:$D$500,Data!$B$2:$B$500,H2,Data!$C$2:$C$500,I2)

Google Sheets has UNIQUE too, and it works on a two-column range the same way. If the spill range is longer than you expect, some merchants are spelled in two ways, for example with and without a trailing space. Clean those first, since each variant counts as a separate merchant.

How do I find the largest total in each category?

List categories once in A2:A9. In B2:

  • =MAXIFS($J$2:$J$300,$H$2:$H$300,A2)

MAXIFS returns the largest value in J where the category in H matches A2. It exists in Excel 2019 and later, and in Google Sheets. Format B as a number with the rupee symbol if you like.

How do I name the merchant?

In C2, for Excel 365 or 2021:

  • =XLOOKUP(1,($H$2:$H$300=A2)*($J$2:$J$300=B2),$I$2:$I$300,"none")

The two comparisons produce arrays of TRUE and FALSE; multiplying them gives 1 only where both the category and the total match. XLOOKUP finds the first 1 and returns the merchant beside it. The last argument is shown if nothing matches. In Google Sheets, use INDEX and MATCH, wrapped so the array maths works:

  • =INDEX($I$2:$I$300,MATCH(1,ARRAYFORMULA(($H$2:$H$300=A2)*($J$2:$J$300=B2)),0))

In older Excel without dynamic arrays, the same INDEX and MATCH works if you confirm it with Ctrl+Shift+Enter.

A hypothetical result, with figures you can check:

CategoryTop merchantTheir total (₹)Category total (₹)
FoodNeighbourhood grocer3,1506,450
TransportFuel station5,20010,200
ShoppingOnline marketplace4,8007,100

For Food, imagine a grocer at 3,150, a delivery app at 2,400 and a tea stall at 900: those sum to 6,450, and the grocer wins. For Transport, fuel at 5,200, a cab service at 3,800 and a metro card at 1,200 add up to 10,200. For Shopping, the online marketplace at 4,800 plus 2,300 spread over smaller sellers gives 7,100.

To see the second or third merchant instead of only the first, use LARGE. In Excel 365, =LARGE(IF($H$2:$H$300=A2,$J$2:$J$300),2) returns the second-highest total in that category, and the same XLOOKUP idea names the merchant. In Google Sheets wrap the formula in ARRAYFORMULA. In older Excel, confirm it with Ctrl+Shift+Enter.

A PivotTable offers a formula-free alternative. Put Category and Merchant in Rows and Amount in Values, then open the filter on Merchant, choose Value Filters, then Top 10, and set it to 1 by Sum of Amount. Excel applies the filter inside each category, so you see one merchant per category. Check the result against your helper table the first time.

What can I do with the answer?

Share of the category held by the top merchant is =B2/SUMIFS($J$2:$J$300,$H$2:$H$300,A2). If one merchant takes most of a category, that is where a lower price or a switch makes a difference. If the category is spread across many merchants, hunting for savings in any single one will not move much.

Checklist

  • Clean merchant names before counting.
  • Check helper totals add up to the data total.
  • Treat ties knowingly.
  • Remember XLOOKUP needs Excel 365 or 2021.

SpendixAI lets you add merchant aliases, so different spellings of one shop are treated as the same merchant before you build reports.

SPENDIXAI JOURNAL

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

Explore your reports