Replace a nested IF with IFS, SWITCH or a lookup table
A nested IF with five levels is hard to read and easy to break. When IFS, SWITCH or a small lookup table does the same job better, with one example each.
To replace a nested IF, use IFS when each test is a range of numbers. Use SWITCH when each test is "equals this code". Use a small lookup table when the numbers in the rule will change.
Here is a sheet from a web shop. Each order has a value and a one-letter status code. Shipping costs less as the order gets bigger, and is free from 100.
| Row | A | B | C |
|---|---|---|---|
| 1 | Order | Value | Status |
| 2 | 1001 | 18.50 | P |
| 3 | 1002 | 42.00 | S |
| 4 | 1003 | 75.00 | D |
| 5 | 1004 | 25.00 | S |
| 6 | 1005 | 120.00 | R |
| 7 | 1006 | 49.99 | X |
The rule: under 25 pays 5.95. From 25 it is 3.95. From 50 it is 1.95. From 100 it is free.
The nested IF you start with
This is the formula most shops have somewhere, in D2 and filled down:
=IF(B2>=100,0,IF(B2>=50,1.95,IF(B2>=25,3.95,5.95)))
It works. Order 1001 gets 5.95, order 1003 gets 1.95 and order 1005 gets 0. Order 1004 is exactly 25, so it gets 3.95.
The trouble starts when you change it. There are 3 IFs, so there are 3 brackets at the end. A fifth band means a fourth IF and a fourth bracket. And the prices are inside the formula, so nobody can see them on the sheet.
The order of the tests matters too. Excel stops at the first test that is TRUE. Write the lowest band first and it catches everything:
=IF(B2>=25,3.95,IF(B2>=50,1.95,IF(B2>=100,0,5.95)))
Order 1005 is 120, but it gets 3.95, not 0. Every order from 25 up gets 3.95. There is no error message.
IFS: the same tests, no brackets
IFS takes pairs: a test, then the answer for that test. Excel reads the pairs from the left and stops at the first TRUE.
=IFS(B2>=100,0,B2>=50,1.95,B2>=25,3.95,TRUE,5.95)
It gives the same 6 answers as the nested IF. The last pair starts with TRUE. That is how IFS says "for everything else".
Leave out the TRUE pair and order 1001 shows #N/A. Its value is
18.50, and none of the 3 tests is TRUE. So always end IFS with a TRUE pair.
IFS does not fix the order problem. The tests still run from the left. Put the highest band first, as in the nested IF.
IFS and SWITCH need Excel 2019 or later, or Microsoft 365. In Excel 2016 the cell shows
#NAME?. Check what your colleagues use before you send the file.
SWITCH: when every test is "equals"
The status column has codes. P is paid, S is shipped, D is delivered and R is returned. With IF, that is 4 IFs:
=IF(C2="P","Paid",IF(C2="S","Shipped",IF(C2="D","Delivered",IF(C2="R","Returned","Check code"))))
SWITCH names the cell once, then lists each code with its answer:
=SWITCH(C2,"P","Paid","S","Shipped","D","Delivered","R","Returned","Check code")
The last argument has no partner. It is the default, for any code that is not on the list. Order 1006 has the code X, so it shows Check code.
Leave out the default and order 1006 shows #N/A. In a status
column, keep a default like Check code. Then a new code from the system is easy to find with a
filter.
SWITCH cannot test "100 or more". It only checks if a value is equal to each item on its list. For bands, use IFS or a table.
A lookup table: when the numbers will change
Put the bands in cells. Here they are in F1:G5. Each row holds the lowest value of a band and its charge.
| Row | F | G |
|---|---|---|
| 1 | From | Charge |
| 2 | 0 | 5.95 |
| 3 | 25 | 3.95 |
| 4 | 50 | 1.95 |
| 5 | 100 | 0 |
=XLOOKUP(B2,$F$2:$F$5,$G$2:$G$5,,-1)
The -1 means "an exact match, or the next smaller value". Order 1002 is 42. There is no 42 in column F, so XLOOKUP takes 25, the next smaller value. The charge is 3.95.
In older Excel, use =VLOOKUP(B2,$F$2:$G$5,2,TRUE). It gives
the same 6 answers. It needs column F sorted from smallest to largest.
Now a price change is a change to one cell. A new band is a new row. Nobody has to read a formula to see the rule, because the rule is on the sheet. XLOOKUP needs Excel 2021 or later, or Microsoft 365.
Put 0 in the first row. Without it, a value below the first band has no row. It then shows
#N/A.
The approximate match lookup article shows how this kind of lookup finds the right band.
Which one to use
- 2 answers: a plain IF. Nothing is simpler.
- 3 or 4 bands that will not change: IFS, with a TRUE pair at the end.
- A short list of codes: SWITCH, with a default at the end.
- Prices or rates that change: a table and XLOOKUP.
What I do: shipping charges, commission rates and tax bands always go in a table. Someone will change them next year. A long code list goes in a table too, if 2 sheets use it.
Mistakes to look for
A number in quote marks.
=IF(B2>="100",0,5.95) gives 5.95 for order 1005, even though
120 is more than 100. "100" is text. Excel puts every number below every text in a comparison.
So the test is never TRUE.
Tests in the wrong order. This is the same in IF and IFS. The loosest test first gives one answer to every row, and no error.
No default. IFS without TRUE and SWITCH without a last argument both show
#N/A for a value that matches nothing.
For more on the IF shape itself, try the IF function exercises. The XLOOKUP vs VLOOKUP article explains both lookups.
The lessons below have the same kind of sheet. Each lesson is a real sheet you type into, and each answer is checked when you press Enter.