XLOOKUP can return several columns at once. Point its return_array at a block like B2:D200 instead of one column, and the formula spills the whole matching row across: =XLOOKUP(F2,$A$2:$A$200,$B$2:$D$200,{"Uncategorised","-","-"}). One formula gives you category, subcategory and tag. It needs Excel 365 or Excel 2021.
How should the merchant table be laid out?
Put your lookup table on its own sheet, say Rules, with one merchant per row and the outputs next to each other:
| A: Merchant | B: Category | C: Subcategory | D: Tag |
|---|---|---|---|
| Swiggy | Food | Delivery | Weekend |
| DMart | Groceries | Supermarket | Monthly |
| Airtel | Bills | Mobile | Fixed |
| Metro card | Travel | Local | Commute |
Now on your transactions sheet, with the cleaned merchant in F2, type the formula in G2. The answer fills G2, H2 and I2. Copy G2 down the column and each row gets its own three cells. The spill needs those cells empty, otherwise you see #SPILL!.
What does each argument do?
- Lookup value: F2, the merchant you are matching.
- Lookup array: $A$2:$A$200, the merchant names. It must be one column.
- Return array: $B$2:$D$200, three columns with the same number of rows as the lookup array.
- If not found: {"Uncategorised","-","-"} is an array constant with three items, so a missing merchant fills all three cells rather than only the first.
- Match mode is left out, so it matches exactly and ignores capitals.
If you wrote a single word, "Uncategorised", as the fourth argument, it would fill only the first cell, not the other two cells in the row. That is why the three-item version is better here.
How do I return only two of the three columns?
XLOOKUP returns whole blocks, so you shape the block. Three ways, easiest first:
- Reorder the table so the two columns you want sit side by side, then point the return array at just those two.
- In recent Microsoft 365, wrap the return array in CHOOSECOLS: =XLOOKUP(F2,$A$2:$A$200,CHOOSECOLS($B$2:$D$200,1,3),{"Uncategorised","-"}) returns Category and Tag, skipping Subcategory.
- Run two ordinary XLOOKUPs, one per column, if your build lacks CHOOSECOLS.
Remember the not-found array must have as many items as the columns you return.
What do the results look like on real rows?
Take three transaction rows with the cleaned merchant in column F. Swiggy returns Food, Delivery, Weekend across G, H and I. Metro card returns Travel, Local, Commute. A merchant not in your table, say Raju Tailors, returns Uncategorised, a dash and a dash, which is easy to filter on later. You can then run a SUMIFS on column G for category totals and on column H for finer detail, without a second lookup formula.
Why does XLOOKUP miss a merchant I can see in the table?
- A trailing space: "Swiggy " is not "Swiggy". Use TRIM(F2) as the lookup value.
- Different spelling: Dmart versus DMart is fine because the match ignores capitals, but "D Mart" is not.
- The lookup array and return array are different heights, for example A2:A200 and B2:D199. That gives #VALUE!.
- The merchant cell shows text, but holds extra characters from a copied statement line. Retype one to test.
Once the three columns are filled, you can build a category and subcategory summary with SUMIFS on columns G and H. Tags are handy for cross-cutting views, such as everything marked Fixed, which are the costs you cannot easily cut this month.
What next?
A sheet of rules like this works well until merchants multiply. A decent habit is to review the Uncategorised rows once a week and add them to the table. SpendixAI can hold custom categories and sub-categories and add merchant aliases, which suits the same idea once the table is too long to maintain by hand.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


