Spreadsheets

IFS vs nested IF for categorising expenses

Write the same expense categorising logic with nested IF, IFS and SWITCH, compare readability and what happens on no match, and when to use a lookup.

Editorial illustration for IFS vs nested IF for categorising expenses

IFS is easier to read than nested IF once you have three or more rules, and SWITCH is tidier when you match exact merchant names. Beyond about five rules, all three get unwieldy and a lookup table is the better home for your rules. Below are the same three rules written each way, so you can compare them.

The same logic, three ways

Say B2 holds the narration. The rules: a narration containing swiggy is Food, uber is Travel, airtel is Bills, and anything else is Uncategorised.

StyleFormula
Nested IF=IF(ISNUMBER(SEARCH("swiggy",B2)),"Food",IF(ISNUMBER(SEARCH("uber",B2)),"Travel",IF(ISNUMBER(SEARCH("airtel",B2)),"Bills","Uncategorised")))
IFS=IFS(ISNUMBER(SEARCH("swiggy",B2)),"Food",ISNUMBER(SEARCH("uber",B2)),"Travel",ISNUMBER(SEARCH("airtel",B2)),"Bills",TRUE,"Uncategorised")
SWITCH=SWITCH(E2,"Swiggy","Food","Uber","Travel","Airtel","Bills","Uncategorised")

The SWITCH version looks at E2, a cleaned merchant name, because SWITCH compares whole values and cannot test whether a cell contains a word. The other two use SEARCH, which finds a word inside the narration.

Which one is easier to read?

Nested IF buries each answer inside the next test, so the closing brackets pile up at the end and one missing bracket breaks everything. IFS lists condition, answer, condition, answer in a flat row, and you can read it from left to right. SWITCH is the shortest, but only for exact matches.

What happens when nothing matches?

  • Nested IF: the innermost else value is what you typed, here "Uncategorised"
  • IFS: no built-in else. If no condition is TRUE, you get #N/A. The pair TRUE,"Uncategorised" at the end plays the part of else
  • SWITCH: an optional last value with no match partner acts as the default, as in the example

Forgetting the TRUE catch-all in IFS is the most common mistake, and the #N/A it produces spreads into every total that reads the column.

Where do they work?

FunctionExcelGoogle Sheets
IFAll versionsYes
IFSExcel 2019 and Microsoft 365Yes
SWITCHExcel 2019 and Microsoft 365Yes

If anyone opening the file uses Excel 2016 or older, nested IF is the safe choice.

What about rules based on the amount?

Sometimes the amount, not the name, decides the category. Say a payment to a society account is Maintenance when it is ₹3,500 or more and Parking below that, using hypothetical figures. IFS handles it neatly: =IFS(D2>=3500,"Maintenance",D2>0,"Parking",TRUE,"Check"). Nested IF needs one more bracket for each rule, and SWITCH cannot do ranges at all, since it only compares for equality. Mixed rules, with names and amounts together, are where IFS earns its keep.

When should I stop and use a lookup table?

Every new merchant means opening the formula and adding another pair. With ten rules, the formula runs across a screen, and nobody wants to edit it. Past about five rules, move the pairs into a two-column table and use a lookup such as =IFNA(VLOOKUP(E2,$H$2:$I$100,2,FALSE),"Uncategorised"). New rules become new rows, and the formula never changes. For keyword matching on partial text, a rule table with SEARCH and MATCH, ranked by priority, is the usual next step.

One more difference shows up while editing. In nested IF, changing the order of two rules means carefully cutting and pasting between brackets. In IFS, you swap two condition and answer pairs, and in a lookup table you simply drag a row up or down. The easier the edit, the more likely you are to keep your rules accurate when a merchant changes its name.

How to decide

  1. Two or three rules in a one-off sheet: any of the three works
  2. Four or five rules, or a sheet others will edit: IFS, or SWITCH with clean names
  3. Older Excel in the mix: nested IF
  4. More than five rules, or rules that change monthly: a lookup table

The more of your own rules you add, the more attractive a tool that manages them for you becomes; SpendixAI lets you create rules, then falls back on your history and similar merchants before any optional AI step.

SPENDIXAI JOURNAL

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

Explore your reports