How to Use the IF Function in Excel to Check a Simple Condition

The IF function is one of the most useful and versatile functions in Excel. It allows you to test a condition and return different values based on whether the condition is true or false. In this article, you will learn the basic theory, the procedures, and some examples of how to use the IF function in Excel.

The IF Function

The syntax of the IF function is as follows:

=IF(logical_test, value_if_true, value_if_false)

The IF function has three arguments:

• logical_test: This is the condition that you want to check. It can be any expression that returns either TRUE or FALSE. For example, A1>10, B2=“Yes”, C3=C4, etc.
• value_if_true: This is the value that you want to return if the logical_test is TRUE. It can be a number, text, another formula, or a reference to a cell or range. For example, 100, “Pass”, AVERAGE(A1:A10), D5, etc.
• value_if_false: This is the value that you want to return if the logical_test is FALSE. It can be the same as value_if_true, or different. For example, 0, “Fail”, SUM(B1:B10), E5, etc.

The IF function evaluates the logical_test and returns the corresponding value_if_true or value_if_false. If you omit the value_if_false argument, the IF function returns FALSE by default.

Procedures of Using the IF Function

To use the IF function in Excel, follow these steps:

1. Select a cell where you want to enter the formula.
2. Type =IF( and then enter the logical_test argument. You can type it directly, or use the mouse or keyboard to select a cell or range that contains the condition. For example, =IF(A1>10,
3. Type a comma (,) and then enter the value_if_true argument. You can type it directly, or use the mouse or keyboard to select a cell or range that contains the value. For example, =IF(A1>10,100,
4. Type another comma (,) and then enter the value_if_false argument. You can type it directly, or use the mouse or keyboard to select a cell or range that contains the value. For example, =IF(A1>10,100,0
5. Type a closing parenthesis ()) and press Enter. The formula is complete and the result is displayed in the selected cell. For example, =IF(A1>10,100,0)

Example of Using the IF Function

To illustrate how to use the IF function in Excel, let’s look at a simple scenario. Suppose you have a list of students and their scores in a test, and you want to assign a grade based on the score. The grading criteria are as follows:

• If the score is greater than or equal to 90, the grade is A.
• If the score is greater than or equal to 80 and less than 90, the grade is B.
• If the score is greater than or equal to 70 and less than 80, the grade is C.
• If the score is greater than or equal to 60 and less than 70, the grade is D.
• If the score is less than 60, the grade is F.

The data is shown in the following table:

To use the IF function to calculate the grade, we can enter the following formula in cell C2 and copy it down to the other cells:

=IF(B2>=90,“A”,IF(B2>=80,“B”,IF(B2>=70,“C”,IF(B2>=60,“D”,“F”))))

This formula uses nested IF functions to check multiple conditions. The logic is as follows:

• First, it checks if the score in B2 is greater than or equal to 90. If yes, it returns “A” as the grade. If no, it goes to the next IF function.
• Second, it checks if the score in B2 is greater than or equal to 80. If yes, it returns “B” as the grade. If no, it goes to the next IF function.
• Third, it checks if the score in B2 is greater than or equal to 70. If yes, it returns “C” as the grade. If no, it goes to the next IF function.
• Fourth, it checks if the score in B2 is greater than or equal to 60. If yes, it returns “D” as the grade. If no, it returns “F” as the grade.

The result is shown in the following table:

Other Approaches to Check a Simple Condition

The IF function is not the only way to check a simple condition in Excel. Depending on the situation, you may use other functions or features to achieve the same result. Here are some examples:

• Use the IFS function to check multiple conditions without nesting. The IFS function is available in Excel 2019 and later versions, and it has the following syntax:

=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], …)

The IFS function evaluates each logical_test in order, and returns the corresponding value_if_true if the logical_test is TRUE. If none of the logical_tests are TRUE, it returns #N/A error. For example, the formula in the previous example can be rewritten as:

=IFS(B2>=90,“A”,B2>=80,“B”,B2>=70,“C”,B2>=60,“D”,B2<60,“F”)

• Use the CHOOSE function to return a value based on an index number. The CHOOSE function has the following syntax:

=CHOOSE(index_num, value1, [value2], …)

The CHOOSE function returns the value that corresponds to the index_num. The index_num can be a number between 1 and 254, or a formula that returns a number. For example, if you have a list of values in a range A1:A5, and you want to return the value in the third position, you can use the formula:

=CHOOSE(3,A1:A5)

This formula returns the value in A3. To use the CHOOSE function to calculate the grade, you can use the formula:

=CHOOSE(MATCH(B2,{0,60,70,80,90},1), “F”,“D”,“C”,“B”,“A”)

This formula uses the MATCH function to return the index number based on the score in B2. The MATCH function looks up the score in B2 in the array {0,60,70,80,90}, and returns the relative position of the first match. The match_type argument is set to 1, which means it finds the largest value that is less than or equal to the lookup value. For example, if the score is 85, the MATCH function returns 4, which means the score is between 80 and 90. Then, the CHOOSE function returns the value in the fourth position, which is “B”.

• Use a lookup table and the VLOOKUP or HLOOKUP function to return a value based on a condition. A lookup table is a range of cells that contains the criteria and the corresponding values. For example, you can create a lookup table for the grading criteria as follows:

The VLOOKUP function looks up a value in the first column of a table, and returns the value in the same row from another column. The syntax is:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

The HLOOKUP function looks up a value in the first row of a table, and returns the value in the same column from another row. The syntax is:

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

The lookup_value is the value that you want to look up. The table_array is the range of cells that contains the lookup table. The col_index_num or row_index_num is the column or row number that contains the value to return. The range_lookup is an optional argument that specifies whether you want an exact match or an approximate match. If you omit it, or set it to TRUE, it returns an approximate match. If you set it to FALSE, it returns an exact match.

To use the VLOOKUP function to calculate the grade, you can use the formula:

=VLOOKUP(B2,G2:H6,2,TRUE)

This formula looks up the score in B2 in the first column of the table G2:H6, and returns the value in the second column. The range_lookup argument is set to TRUE, which means it returns an approximate match. For example, if the score is 85, the V-LOOKUP function returns the value in the same row from the second column, which is “B”.

To use the HLOOKUP function to calculate the grade, you can use the formula:

=HLOOKUP(B2,G1:H6,3,TRUE)

This formula looks up the score in B2 in the first row of the table G1:H6, and returns the value in the same column from the third row. The range_lookup argument is set to TRUE, which means it returns an approximate match. For example, if the score is 85, the HLOOKUP function returns the value in the same column from the third row, which is “B”.