IF Function

The Excel IF function uses a logical test and returns two values, one result for TRUE and another for FALSE.

Use the IF function to perform a logical test and the greater than logical operator to check the condition. For example, if the value in cell A1 is greater than 100, the result is TRUE; else FALSE.

=IF(A1 > 100, ”High”, ”Low”)

How to use the IF Function

We use the IF function to evaluate a logical test (specific condition) and generate two output types for two different purposes (TRUE or FALSE).

Syntax, Arguments

The syntax is simple:

=IF(logical test, [value if true], [value if false])

The function uses three arguments: one required and two optional.

  • logical_test: a single value or a logical expression. The result is a TRUE or FALSE that follows the boolean logic.
  • value_if_true: the result if the logical_test evaluates to TRUE (optional argument)
  • value_if_false: the result if the logical_test evaluates to FALSE (optional argument)

IF and Logical operators

The standalone IF function is effective. However, it is worth combining with other logical functions for extended usability. In the example below, you can create custom tests if you are using the following logical operators for comparison purposes:

Logical operatorNoteExample
=equal toA1 = B1
>greater thanA1 > B1
>=greater than or equal toA1 >= B1
<less thanA1 < B1
<=less than or equal toA1 <= B1
<>not equal toA1 <> B1

Using IF with logical functions: AND, OR, NOT

You can create complex criteria by combining the IF function with the AND or OR function. In the first example, we create a formula that returns “Found” when the number in cell B2 is greater than 5 and less than 20. Otherwise, the formula uses the third argument and returns “not found”.

IF function with AND function
=IF(AND(B2>5,B2<20,"Found","")

Explanation: First, the AND function checks both criteria. In the case of B2>5 and B2<20, the formula returns with TRUE. IF function will use the result as the first argument and return with “match”. Otherwise, the formula returns “no match“.

Here is an IF and OR example; create a formula using the following criteria. If B2 is equal to 10 or 20, return the “match found” value; else, display “no match”.

OR function combined with IF
=IF(OR(B2=10,B2=20),"match found","no match")

In the third example, we have complex criteria: If B2 is between 10 and 20, or B3 is between 80 and 100, return the message “match found“, elsewhere “no match“.

Using IF function with logical functions  AND, OR, NOT
=IF(OR(AND(B2>10,B2<20), AND(B3>80,B2<100),"match found","no match")

Finally, here is an example with the IF and NOT function. If B2 is NOT “t-shirt”, return B2 * 0.9; otherwise, return B2.

=IF(NOT(B2="t-shirt"), B2*0.9 ,B2)

Nested IF statements

Since we have the IFS function, the nested IF solutions seem outdated. You can entwine a maximum of 128 IF functions.

A “nested IF” refers to a formula that contains another IF function. In this case, you can test more conditions in a single formula, and variable outputs are available.

Here is a simple rating example, test cell B2 and return the corresponding value based on the following rules: 1= “bad”, 2 = “poor”, 3 = average, 4 = “good”, 5 = “excellent”

Check the rating in cell B3:

=IF(B3=1,"bad",IF(B3=2,"poor",IF(B3=3,"average",IF(B3=4, "good",IF(B3=5,"excellent")))))
Nested IF statements

Here is the simplified formula using IFS:

=IFS(B3=1,"bad",B3=2,"poor",B3=3,"average",B3=4,"good",B3=5,"best")

Compare the formula structures; we prefer the IFS function instead of using IF.

Use logical test without IF

Because the output is always TRUE or FALSE, we’ll use a formula without functions in the following example.

=A1 > 100

if function without if

I think no further explanation is necessary. If the price is greater than 100, the result is TRUE. Else the formula returns FALSE.

Additional resources:

Istvan Vozar

Istvan is the co-founder of Excelkid. He writes blog posts and helps people to reach the top in Excel.