IF vs IFS: nested IFs made readable
IF makes one decision. Several decisions used to mean nesting IF inside IF. IFS lists the tests in order and returns the first one that’s true.
| IF | IFS | |
|---|---|---|
| Decisions | One (nest for more) | Many, in order |
| “Otherwise” value | Built in (3rd argument) | Add TRUE,"value" at the end |
| If nothing matches | Returns the else value | #N/A without a TRUE catch-all |
| Available in | Every version | Excel 2019+, Microsoft 365, Google Sheets |
Side by side
IF
=IF(F6>=100,"Large",IF(F6>=50,"Medium","Small")) → Large
IFS
=IFS(F6>=100,"Large",F6>=50,"Medium",TRUE,"Small") → Large
Examples use this sample sheet of orders:
A
B
C
D
E
F
1
Order
Region
Rep
Units
Price
Revenue
2
1001
East
Maya
4
12
48
3
1002
West
Omar
2
30
60
4
1003
East
Lena
7
12
84
5
1004
North
Maya
1
45
45
6
1005
West
Lena
5
30
150
7
1006
East
Omar
3
18
54
8
1007
North
Lena
6
12
72
9
1008
West
Maya
8
18
144
Which should you use?
Use IF for a single yes/no. Switch to IFS once you’d need a second nested IF.