Logical
IF function in Excel:syntax and examples
IF checks a condition and returns one value if it is met and another if it is not. In Spanish Excel it is called SI. Syntax: =IF(logical_test, value_if_true, value_if_false).
Syntax
=IF(logical_test, value_if_true, [value_if_false])In English Excel, arguments are separated with a comma (,). In Spain and much of Europe and Latin America, Excel uses a semicolon (;).
Arguments
| Argument | What it is |
|---|---|
logical_test | Required. The question, which can only be answered with TRUE or FALSE. Example: B2>=50. |
value_if_true | Required. What it returns if the condition is met: text in quotes, a number, a cell or a calculation. Example: "Pass". |
value_if_false | Optional. What it returns if the condition is not met. If you leave it out, IF returns FALSE. Example: "Fail". |
How do you use the IF function? A step-by-step example
IF answers a yes-or-no question and writes a different result in each case. In this example you have the scores of 5 students and you want to mark who passes with 50 or more.
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | Submitted project |
| 2 | Lucy Grant | 75 | Yes |
| 3 | Mark Rivera | 48 | Yes |
| 4 | Valerie Cruz | 50 | No |
| 5 | Daniel Ford | 92 | Yes |
| 6 | Cameron Hayes | 31 | Yes |
In D2 you type =IF(B2>=50,"Pass","Fail") and drag down to D6. Lucy gets Pass and Mark, with 48, gets Fail.
B2>=50is the question: is the score 50 or more?"Pass"is what it writes if the answer is yes. Text goes in quotes."Fail"is what it writes if the answer is no.
The operators you can use in the question are =, <> (not equal to), >, <, >= and <=.
How do you write an IF with multiple conditions?
It depends on whether all conditions must be met, one is enough, or there are more than two results.
| You want | Use | Example | Result |
|---|---|---|---|
| All conditions met | IF with AND | =IF(AND(B4>=50,C4="Yes"),"Pass","Fail") | Fail: Valerie has a 50, but did not submit the project |
| At least one condition met | IF with OR | =IF(OR(B3>=50,C3="Yes"),"Eligible","Not eligible") | Eligible: Mark fails, but he submitted |
| Three results or more | Nested IF | =IF(B5>=90,"Distinction",IF(B5>=50,"Pass","Fail")) | Distinction for Daniel, with 92 |
In a nested IF, Excel reads from left to right and keeps the first condition that is met. That is why you start with the strictest (90) and end with the loosest (50). With Lucy's score, the same formula returns Pass; with Cameron's, Fail.
Nested IF or IFS?
IFS writes several conditions in a row without nesting parentheses. It is in Microsoft 365 and Excel 2019 and later. If your Excel is older, or the test runs on a machine you do not know, use a nested IF.
| Nested IF | IFS | |
|---|---|---|
| Versions | All | Excel 2019 and later, Microsoft 365 |
| Parentheses | One per IF | One |
| If no condition is met | Returns the last value_if_false | Returns #N/A, unless the last condition is TRUE |
| When to pick it | Up to 3 results, or when you do not know the version | 4 results or more, in Excel 2019 or later |
With Cameron's score, =IFS(B6>=90,"Distinction",B6>=50,"Pass") returns #N/A because neither condition is met. Add TRUE,"Fail" at the end and it returns Fail.
With more than 4 bands (commissions, rates, grades), a table of bands with VLOOKUP and TRUE is easier to maintain: you change a number in the table and leave the formula alone.
How does the IF function show up in an Excel test?
In a job interview the classic is a commission: "If the salesperson sells more than 10,000, they earn a 5% commission; otherwise, nothing." The expected answer is =IF(B2>10000,B2*5%,0). What they look for: the result is a calculation, not text, and the 5% is not in quotes.
In a skills test it usually looks like this:
What does
=IF(B3>=50,"Pass")return if B3 is 48? a) Fail · b) An empty cell · c) FALSE · d) #VALUE!
Answer: c. Without the third argument, IF returns FALSE when the condition is not met.
To count how many students passed instead of marking each one, use COUNTIF: =COUNTIF(B2:B6,">=50") returns 3. To add up only the rows that meet a condition, use SUMIF.
Practice the IF function
The English exercises are on the way. Until then, copy the example above into a blank sheet and try it with your own scores.
Available in: all versions of Excel. Source: Microsoft: IF function. Results calculated with Formulo's engine.
Common mistakes
- #NAME?: the text is missing its quotes
- =IF(B2>=50,Pass,"Fail") returns #NAME? because Excel reads Pass as the name of something that does not exist. Every piece of text goes in quotes: "Pass".
- FALSE shows up in the cell
- The third argument is missing. =IF(B3>=50,"Pass") returns FALSE when the score is below 50. Add what you want to see in that case, even "" to leave the cell looking empty.
- A 50 counts as a fail
- You used > instead of >=. With a score of 50, =IF(B4>50,"Pass","Fail") returns Fail and =IF(B4>=50,"Pass","Fail") returns Pass.
- A score stored as text always passes
- If the score is stored as text (a "30" aligned to the left), Excel treats any text as larger than any number. =IF(E2>=50,"Pass","Fail") returns Pass even when E2 holds "30". Convert the column to numbers.
- The nested IF returns the result of another band
- In a nested IF, Excel keeps the first condition that is met. If you start with B2>=50, a score of 92 never reaches "Distinction". Order the conditions from the strictest to the loosest.
Frequently asked questions
What is the IF function called in Spanish?
SI. In a Spanish Excel, TRUE and FALSE become VERDADERO and FALSO, and the separator is a semicolon: =SI(B2>=50;"Pass";"Fail").
How many IFs can I nest?
Excel allows up to 64 nested IF functions. Past 3 or 4 the formula is hard to read: use IFS or a table of bands with VLOOKUP.
Is IF case-sensitive?
No. If C3 contains Yes, =IF(C3="yes","Handed in","Pending") returns Handed in.
How do I make IF leave the cell blank?
Use two quotes in a row as the result: =IF(B2>=50,"Pass",""). The cell looks empty, although it holds an empty text.
Can IF return a calculation instead of text?
Yes. =IF(B2>=50,B2*1.1,B2) raises the score by 10% only for those who passed: with 75 it returns 82.5.
What is the difference between IF and IFERROR?
IF checks the condition you write. IFERROR only checks whether a formula returns an error and, if it does, shows another value.
Related functions
Practice in a real spreadsheet
Exercises with real data and instant checking. The first three are free.