Excel IF Function

last modified October 5, 2026

The IF function is one of the most powerful and frequently used functions in Excel. It performs a logical test and returns one value when the test is TRUE and another when it is FALSE. This tutorial provides a comprehensive guide to using the IF function with detailed examples. You'll learn the basic syntax, omitted arguments, the blank-cell trap, nested IF pitfalls, and how to combine IF with AND, OR, and the IS functions.

IF Function Basics

The IF function evaluates a condition and returns one value if it is true and another if it is false. It enables decision-making in Excel formulas. The syntax is straightforward but highly flexible.

Component	Description
Function Name	IF
Syntax	=IF(logical_test, [value_if_true], [value_if_false])
Required Argument	logical_test
Optional Arguments	value_if_true, value_if_false
Return Value	value_if_true when the test is TRUE, value_if_false when it is FALSE

This table breaks down the essential components of the IF function. It shows the function name, syntax format, required and optional arguments, and return value behavior.

Argument	Meaning
logical_test	Any expression that evaluates to TRUE or FALSE, for example A1>10 or A1="Apple". A non-zero number counts as TRUE and zero counts as FALSE.
value_if_true	The value returned when the test is TRUE. It can be a number, text in quotes, a cell reference, or a formula. If it is left empty, the result is 0.
value_if_false	The value returned when the test is FALSE. It accepts the same kinds of values as value_if_true. If it is omitted, the result is the logical value FALSE.

Only logical_test is strictly required. The other two arguments are optional, but their defaults are easy to overlook, as the next section shows.

Omitted Arguments and Their Defaults

When you leave out one of the result arguments, Excel fills in a default rather than returning a blank. Leaving value_if_true empty returns 0 when the test is TRUE, and omitting value_if_false returns the logical value FALSE when the test is FALSE.

=IF(A1>10, "High")          ' FALSE returns the logical value FALSE
=IF(A1>10, , "Low")         ' TRUE returns 0
=IF(A1>10, "High", "Low")   ' both results supplied

To return an empty-looking result instead of 0 or FALSE, pass an empty string as the argument: =IF(A1>10, "High", ""). The empty string displays as nothing while keeping the cell's value as text.

Basic IF Example

This example demonstrates the simplest use of the IF function with a numeric comparison.

=IF(A1>10, "High", "Low")

This formula checks if the value in cell A1 is greater than 10. If true, it returns "High"; if false, it returns "Low". This shows IF's basic conditional logic structure.

IF with Text Comparison

IF can compare text values as well as numbers. Here is an example checking whether a cell contains specific text.

A	B
Apple
Orange
Banana
	=IF(A1="Apple", "Fruit", "Other")

The table shows a simple spreadsheet with text values in column A and an IF formula in cell B4 that checks if A1 contains "Apple". Since A1 holds "Apple", the formula returns "Fruit".

=IF(A1="Apple", "Fruit", "Other")

This formula checks if cell A1 equals "Apple". If true, it returns "Fruit"; otherwise "Other". Text comparisons in IF are case-insensitive by default, so "apple" and "APPLE" also match "Apple".

Comparisons and the Blank-Cell Trap

Comparisons follow Excel's value order, in which numbers come first, text comes after numbers, and logical values come last. As a result, any text is considered greater than any number.

Comparison	Result	Why
A1="apple" when A1 is "Apple"	TRUE	Text comparisons ignore case
"Apple">100	TRUE	Text sorts after numbers
A1=0 when A1 is empty	TRUE	A blank cell is treated as 0
A1="" when A1 is empty	TRUE	A blank cell matches the empty string
""=0	FALSE	An empty string is not equal to 0

The blank-cell behavior is the most common source of surprises. An empty cell compares equal to both 0 and the empty string, so a test such as =IF(A1=0, "Zero or blank", "Not zero") is TRUE for a truly empty cell. The literal empty string, however, is not equal to 0. To tell a blank cell apart from a cell that contains 0, test it directly with ISBLANK.

=IF(A1=0, "Zero or blank", "Not zero")   ' blank and 0 both match
=IF(ISBLANK(A1), "Truly empty", "Not empty")
=IF(ISNUMBER(A1), "Number", "Not a number")

ISBLANK returns TRUE only for a truly empty cell, so a cell holding 0 or an empty string is reported as not blank. Use it whenever the difference between "no data" and "zero" matters.

Nested IF and Its Pitfalls

Multiple IF functions can be nested to handle several conditions. This example shows a grading system with multiple thresholds.

A	B
85
	=IF(A1>=90, "A", IF(A1>=80, "B", IF(A1>=70, "C", "D")))

The table demonstrates nested IF functions to assign letter grades based on a numeric score. The formula checks the conditions in sequence.

=IF(A1>=90, "A", IF(A1>=80, "B", IF(A1>=70, "C", "D")))

This formula first checks if the score is 90 or higher. If not, it checks for 80 or higher, then 70 or higher, and finally defaults to "D". Because A1 holds 85, the result is "B". Nested IFs allow complex decision trees, but they have real drawbacks: each condition adds another level of nesting, the formula becomes hard to read, and Excel supports at most 64 nested IF levels. The order of the tests also matters, since Excel stops at the first TRUE condition.

When you have more than a few conditions, prefer IFS, which reads linearly and does not need a closing parenthesis for every branch. The nested formula above becomes the following.

=IFS(A1>=90, "A", A1>=80, "B", A1>=70, "C", TRUE, "D")

IFS evaluates each condition and value pair in order and stops at the first TRUE condition. The final TRUE acts as a default and is important: without a matching condition, IFS returns #N/A. For exact-value matching, SWITCH is often even clearer.

=SWITCH(A1, 1, "Gold", 2, "Silver", 3, "Bronze", "Other")

SWITCH compares one expression against a list of values and returns the result that follows the first match; the last argument is the default when nothing matches.

IF with AND/OR Functions

IF can combine with AND and OR to test several conditions at once. AND requires every condition to be TRUE, while OR requires at least one.

A	B	C
25	5000
		=IF(AND(A1>18, B1>3000), "Approved", "Denied")

The table shows how to use IF with AND to require both an age (A1) and an income (B1) criterion for approval.

=IF(AND(A1>18, B1>3000), "Approved", "Denied")

This formula checks if the age is over 18 AND the income exceeds 3000. Both conditions must be true for approval; here the result is "Approved". Replacing AND with OR returns "Approved" when either condition is met, which is useful for alternative criteria.

=IF(OR(A1="Red", A1="Blue"), "Primary", "Other")

IF with Mathematical Operations

IF can return different calculations based on a condition. This example applies different discount rates depending on the purchase amount.

A	B
1200
	=IF(A1>1000, A1*0.9, A1*0.95)

The table demonstrates using IF to choose between different mathematical operations based on the value in cell A1.

=IF(A1>1000, A1*0.9, A1*0.95)

This formula applies a 10% discount if the purchase exceeds 1000, otherwise a 5% discount. With A1 equal to 1200, the result is 1080. IF can return different calculations based on conditions.

IF with IS Functions

The IS family of functions returns TRUE or FALSE about the nature of a value, which makes them natural companions for IF. This example shows how to handle missing data.

A	B
Data
	=IF(ISBLANK(A1), "Missing", "Present")

The table contains blank and non-blank cells to demonstrate how IF handles empty cells when combined with ISBLANK. Because A1 is blank, the formula returns "Missing".

=IF(ISBLANK(A1), "Missing", "Present")

This formula checks if A1 is blank, returning "Missing" if true or "Present" if false. ISBLANK helps manage incomplete datasets in conditional logic.

IF with Error Handling

IF can detect and handle errors using ISERROR, or you can use IFERROR, which wraps the test and the fallback in one function. This example prevents an error from being displayed.

A	B
10
0
	=IF(ISERROR(A1/A2), "Error", A1/A2)

The table shows how to use IF with ISERROR to handle a potential division-by-zero error gracefully. Because A2 is 0, A1/A2 produces #DIV/0!, so the formula returns "Error".

=IF(ISERROR(A1/A2), "Error", A1/A2)

This formula attempts the division but returns "Error" if the operation fails. The shorter IFERROR form below does the same job and also catches #N/A, #VALUE!, and other error types.

=IFERROR(A1/A2, "Error")

IF with Date Comparisons

IF can compare dates and return date-based results. Excel stores dates as serial numbers, so ordinary comparison operators work on them.

A	B
4/15/2025
	=IF(A1>DATE(2026,1,1), "Future", "Past")

The table compares the date in A1 with a fixed cutoff of January 1, 2026. Because April 15, 2025 comes earlier, the formula returns "Past".

=IF(A1>DATE(2026,1,1), "Future", "Past")

The DATE function builds the comparison date from year, month, and day, which keeps the formula stable. Replace it with TODAY() to compare against the current date; keep in mind that TODAY() recalculates every day, so the result changes over time.

IF with Array Formulas

In modern Excel, IF can work with array formulas to process many values at once. This example applies a condition to a range.

=SUM(IF(A1:A10>50, A1:A10, 0))

This array formula sums only the values in A1:A10 that exceed 50, treating the rest as 0. The IF function processes each cell in the range individually and SUM adds the results. In Microsoft 365 and Excel 2021 the formula is confirmed with a plain Enter. In Excel 2019 and earlier it must be confirmed with Ctrl+Shift+Enter, which wraps it in curly braces. When you only need the total, SUMIF avoids the array entry altogether.

=SUMIF(A1:A10, ">50")

IF Errors and Troubleshooting

Cause	Symptom	Fix
value_if_false omitted	Cell shows FALSE instead of a blank	Supply a value, or use "" for an empty result
value_if_true left empty	Cell shows 0 when the test is TRUE	Supply a value, or use "" for an empty result
Text is not wrapped in quotes	#NAME? error	Write text as "text"; only TRUE and FALSE stand alone
Blank cell compared with 0	The test is TRUE for an empty cell	Use ISBLANK to detect truly empty cells
Text compared with a number	Unexpected TRUE or FALSE	Remember that text sorts after numbers
More than three arguments	You've entered too many arguments error	IF accepts at most three arguments
Nested IF too deep	The formula is hard to maintain or stops working	The limit is 64 levels; switch to IFS
IFS with no matching condition	#N/A error	Add a final TRUE condition as a default
Array formula entered normally in older Excel	Wrong result or #VALUE! error	Confirm with Ctrl+Shift+Enter, or use SUMIF

Most IF problems come from its defaults and from comparing values of different types. Supply both result arguments when the defaults are not what you want, and reach for ISBLANK or ISNUMBER before comparing a cell with a number.

The IF function is essential for all Excel users. From simple conditional checks to complex decision trees, IF handles diverse logic needs. Mastering IF together with its companions (AND, OR, the IS functions, and IFS) will significantly improve your spreadsheet capabilities. Remember that IF can return values, calculations, or even other functions based on a condition, that omitted arguments fall back to 0 or FALSE, and that a blank cell compares equal to 0. Keep those details in mind and your formulas will behave exactly as intended.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Excel Formulas.