How to Use the IF Function in Excel

The IF function is one of the most useful and versatile functions in Excel. It allows you to perform a logical test on a value or expression and return one result if the test is true, and another result if the test is false.

The syntax of the IF function is:

=IF(logical_test, value_if_true, value_if_false)

• logical_test is any expression that can be evaluated as TRUE or FALSE, such as A1>10, B2=“Yes”, or C3=D3.
• value_if_true is the value or formula that you want to return if the logical test is true. You can also use another IF function as the value_if_true argument, creating a nested IF function.
• value_if_false is the value or formula that you want to return if the logical test is false. You can also use another IF function as the value_if_false argument, creating a nested IF function.

How to Use the IF Function to Check Cells in Worksheets and Workbooks

You can use the IF function to check the values or formulas in cells across worksheets and workbooks. For example, you can use the IF function to compare the sales figures of different regions, products, or months, and display a message or perform a calculation based on the comparison.

To use the IF function to check cells in worksheets and workbooks, you need to use the following syntax:

=IF('Workbook Name'!Worksheet Name!Cell Reference logical_test, value_if_true, value_if_false)

• 'Workbook Name'!Worksheet Name!Cell Reference is the reference to the cell that you want to check in another workbook or worksheet. You need to enclose the workbook name and the worksheet name in single quotes, and use an exclamation mark to separate them from the cell reference. For example, 'Sales Report.xlsx'!January!A1 refers to cell A1 in the January worksheet of the Sales Report workbook.
• logical_test is the same as in the basic IF function syntax.
• value_if_true is the same as in the basic IF function syntax.
• value_if_false is the same as in the basic IF function syntax.

Example: Using the IF Function to Check Cells in Worksheets and Workbooks

Let’s say you have two workbooks: Sales Report.xlsx and Budget.xlsx. In the Sales Report workbook, you have 12 worksheets, one for each month of the year, with the sales figures of different products in each worksheet. In the Budget workbook, you have one worksheet with the budgeted sales figures of each product for the whole year.

You want to use the IF function to check if the actual sales of each product in each month met the budgeted sales, and display a message “Met” or “Not Met” accordingly.

To do this, you can use the following formula in cell B2 of the January worksheet of the Sales Report workbook:

=IF('Budget.xlsx'!Budget!B2<='Sales Report.xlsx'!January!B2, "Met", "Not Met")

This formula checks if the budgeted sales of product A in cell B2 of the Budget worksheet of the Budget workbook is less than or equal to the actual sales of product A in cell B2 of the January worksheet of the Sales Report workbook. If yes, it returns “Met”, otherwise it returns “Not Met”.

You can copy this formula to the other cells in column B of the January worksheet, and then copy the whole column to the other worksheets of the Sales Report workbook, to check the sales performance of each product in each month.

The result will look something like this:

Other Approaches to Use the IF Function in Excel

There are many other ways to use the IF function in Excel, depending on your needs and preferences. Here are some examples of other approaches:

• You can use the AND, OR, and NOT functions to combine multiple logical tests in the IF function. For example, =IF(AND(A1>10, B1<20), "Pass", "Fail") returns “Pass” if both A1 is greater than 10 and B1 is less than 20, and returns “Fail” otherwise.
• You can use the IFERROR function to handle errors in the IF function. For example, =IFERROR(IF(A1/B1>0.5, "Good", "Bad"), "Invalid") returns “Good” if A1 divided by B1 is greater than 0.5, returns “Bad” if A1 divided by B1 is less than or equal to 0.5, and returns “Invalid” if A1 or B1 is zero or contains an error.
• You can use the IFS function to specify multiple conditions and values in the IF function. For example, =IFS(A1>10, "High", A1>5, "Medium", A1>0, "Low", TRUE, "Negative or Zero") returns “High” if A1 is greater than 10, returns “Medium” if A1 is greater than 5 but less than or equal to 10, returns “Low” if A1 is greater than 0 but less than or equal to 5, and returns “Negative or Zero” if A1 is less than or equal to 0.