Excel logical functions
Logical functions make decisions: flag late orders, pick a shipping rate, show a blank instead of an error. IF does most of the work. AND, OR and NOT build the tests it checks, IFS and SWITCH replace long chains of nested IFs, and IFERROR and IFNA keep errors out of finished reports.
- Functions
- 11
- With a graded exercise
- All 11
- Start with
IF
Which one should I use?
| If you need to… | Use | Why |
|---|---|---|
| Return one result if a test passes and another if it fails | IF | The core decision function. Everything else in this group feeds it or replaces it. |
| Require several conditions at once | AND | Put it inside IF: =IF(AND(B2>=50,C2="Yes"),"Free","Paid"). |
| Pass when any one of several conditions is true | OR | One TRUE is enough, which suits "big order or member" rules. |
| Pick from three or more outcomes without nesting | IFS | A flat list of tests checked in order. The first TRUE one wins. |
| Turn exact codes into labels or numbers | SWITCH | Cleaner than IFS when every test is "equals this value". |
| Show a blank or a message instead of an error | IFERROR | Use IFNA instead when only missing lookups should be hidden. |
Try IF live
The most useful logical function, on a sample sheet you can edit. Change a value and watch the results update.
E2=IF(B2>=200,"Free shipping","Standard") Free shipping Text result based on a number testE3=IF(C3="No",B3*0.05,0) $4.25 A calculation when the test is TRUE: a 5% late fee on an unpaid orderE4=IF(AND(B4>500,C4="No"),"Call customer","") Call customer Two conditions with AND; "" leaves the cell blank when the test fails
All 11 logical functions
| Function | What it does | Practice exercise |
|---|---|---|
AND function | AND returns TRUE only when every condition you give it is TRUE. | Promotion eligibility |
CHOOSE function | CHOOSE returns the item at a given position from a list you type into the formula: 1 returns the first item, 2 the second, and so on. | Bonus by level |
IF function | IF checks a condition and returns one value when it is true and another when it is false. | Pass or fail |
IFERROR function | IFERROR returns a value you choose when a formula results in an error, and the formula's normal result otherwise. | Unknown SKUs |
IFNA function | IFNA returns a fallback only when a formula results in #N/A, the error lookups give when a value is missing. | Line totals with missing SKUs |
IFS functionExcel 2019 and later | IFS checks a list of conditions in order and returns the value paired with the first one that is TRUE. | Letter grades |
LET functionExcel 2021, 2024 and Microsoft 365 | LET gives names to values or calculations inside a single formula, then uses those names in a final calculation. | Discounted totals |
NOT function | NOT flips a logical value: TRUE becomes FALSE and FALSE becomes TRUE. | Reorder list |
OR function | OR returns TRUE when at least one of its conditions is TRUE. | Free shipping rule |
SWITCH functionExcel 2019 and later | SWITCH compares one value against a list of exact matches and returns the result paired with the first match, with an optional default at the end. | Response times by priority |
XOR function | XOR returns TRUE when an odd number of its conditions are TRUE. | Discount rule check |
Head-to-head
- IFERROR vs IFNABoth replace an error with something friendlier. IFNA only catches #N/A, the "not found" error from lookups; IFERROR catches every error, including ones that point to real mistakes.
Learn them in order
These functions are taught step by step in the Logic & conditions track, which is free. Each lesson ends with graded exercises on real-looking data.
- The IF function7 min
- AND, OR & IFS9 min
- Tiered lookups & IFERROR9 min · Pro
- LET8 min · Pro
- Capstone: analyst interview test12 min · Pro