Introduction
Spreadsheets constantly make decisions: pass or fail, bonus or no bonus, in stock or reorder, early, on time or late. The IF function lets a formula choose its result based on a condition, and a few companion functions handle more conditions and catch errors. With these, a sheet stops being a calculator and starts applying rules.
How it works
IF takes three arguments: a logical test, the value to return when it is TRUE, and the value to return when it is FALSE:
=IF(B2>=50,"Pass","Fail")
Text results go in double quotes; numbers and other formulas do not. If you leave out the third argument, a FALSE test returns the word FALSE, so it is better to always give it, even if it is just "" for an empty-looking result.
To test several conditions at once, combine them inside the test:
AND(B2>=50,C2>=50)is TRUE only if all conditions are TRUE.OR(B2>=90,C2="Merit")is TRUE if at least one is TRUE.NOT(...)turns TRUE into FALSE and back.
So =IF(AND(B2>=50,C2>=50),"Pass","Fail") passes only students who passed both papers.
For more than two outcomes, you can nest IFs, putting a second IF in the FALSE argument of the first: =IF(B2>=80,"A",IF(B2>=65,"B",IF(B2>=50,"C","F"))). Excel checks the conditions in order and stops at the first TRUE one, so the order matters: test the highest threshold first. Nested IFs get hard to read beyond two or three levels.
IFS, available in Excel 2019 and later, lists condition and result pairs and returns the result of the first TRUE condition: =IFS(B2>=80,"A",B2>=65,"B",B2>=50,"C",TRUE,"F"). The final TRUE acts as "everything else". Without it, a value that matches no condition returns #N/A.
To catch errors, wrap a formula in IFERROR: =IFERROR(B2/C2,0) returns 0 instead of #DIV/0! when C2 is empty or zero. IFNA catches only #N/A, the error lookups return when they find nothing, and lets every other error through, which makes it the safer choice for lookups. Use both carefully: hiding an error does not fix the problem behind it.
Example
A marks sheet gives grades and flags missing data:
| A | B | C | |
|---|---|---|---|
| 1 | Student | Mark | Grade |
| 2 | Rina | 82 | |
| 3 | Arif | 64 | |
| 4 | Zoya | 49 |
Line by line
- In C2 type
=IFS(B2>=80,"A",B2>=65,"B",B2>=50,"C",TRUE,"F")and fill it down to C4. - In D2 type
=IF(AND(B2>=50,B2<65),"Borderline","")and fill it down. - In B6 type
=IFERROR(AVERAGE(B2:B5),"No marks")for the class average.
Expected result
C2:C4 show A, C and F (Arif's 64 is below 65, so he gets C). D3 shows Borderline and D2 and D4 look empty. B6 shows 65, the average of 82, 64 and 49; if all marks were deleted it would show No marks instead of #DIV/0!.
In the real world
Commission schemes, shipping charges, tax bands, stock reorder flags and status columns are all IF logic. Finance teams often keep the thresholds in a small table and look them up instead, which a later lesson shows, so that a change of rule does not mean editing every formula.
Common mistakes
- Testing thresholds in the wrong order, so a mark of 90 matches 50 first.
- Forgetting the quotes around text results, which gives
#NAME?. - Wrapping everything in IFERROR, hiding real mistakes such as a misspelt range.
- Writing
50<=B2<65as in maths; Excel needsAND(B2>=50,B2<65).
Best practices
- Put thresholds in cells and refer to them instead of typing numbers into IFs.
- Prefer IFS, or a lookup table, to more than two nested IFs.
- Use IFNA rather than IFERROR around lookups, so other errors stay visible.
Keyboard shortcuts
| Action | Windows | Mac |
|---|---|---|
| Insert a function | Shift+F3 | Shift+F3 |
| New line inside a long formula | Alt+Enter | Option+Return |
| Expand or collapse the formula bar | Ctrl+Shift+U | Control+Shift+U |
| Fill a formula down | Ctrl+D | Command+D |
| Cancel editing | Esc | Esc |
Summary
IF returns one value when a test is TRUE and another when it is FALSE. AND and OR combine conditions, NOT reverses one, nested IFs and IFS choose between several outcomes in order, and IFERROR and IFNA replace error values. Keep rules readable and thresholds in cells.
Quick check
Key takeaways
- IF(test, value if TRUE, value if FALSE) is the basis of every decision in a formula.
- AND needs all conditions to be TRUE, OR needs at least one.
- IFS reads more clearly than deeply nested IFs; IFERROR hides errors, so use it on purpose.
My notes
Write down what you want to remember from this lesson. Notes are saved on this device as you type, and appear on your dashboard.