Excel IF Statement

It allows you to compare a number and what you anticipate and then take appropriate actions based on the results.

Author: Adin Lykken
Adin Lykken
Adin Lykken
Consulting | Private Equity

Currently, Adin is an associate at Berkshire Partners, an $16B middle-market private equity fund. Prior to joining Berkshire Partners, Adin worked for just over three years at The Boston Consulting Group as an associate and consultant and previously interned for the Federal Reserve Board and the U.S. Senate.

Adin graduated from Yale University, Magna Cum Claude, with a Bachelor of Arts Degree in Economics.

Reviewed By: Christy Grimste
Christy Grimste
Christy Grimste
Real Estate | Investment Property Sales

Christy currently works as a senior associate for EdR Trust, a publicly traded multi-family REIT. Prior to joining EdR Trust, Christy works for CBRE in investment property sales. Before completing her MBA and breaking into finance, Christy founded and education startup in which she actively pursued for seven years and works as an internal auditor for the U.S. Department of State and CIA.

Christy has a Bachelor of Arts from the University of Maryland and a Master of Business Administrations from the University of London.

Last Updated:November 1, 2023

What is an Excel IF Statement?

The IF statement in Excel is a practical and widely used function that allows you to compare a number and what you anticipate and then take appropriate actions based on the results.

It is a versatile function that may be used in several situations and can be combined with other functions to perform more complex computations.

Excel employs a logical test to determine whether or not the condition is met. 

If the condition is met, the function is set to return the value specified for a valid case. However, if the condition is not met, the function will return the value set for a false scenario.

Based on the conditions supplied (numbers, text, date, blank, etc.), the IF function can perform several actions in various contexts and return any sort of values specified (numbers, text, blank, formula, etc.). 

It is a must-have tool for everyone who has to make decisions or do calculations utilizing data from an Excel spreadsheet.

Key Takeaways

  • An Excel IF statement is a decision-making tool to analyze data and result in appropriate actions based on certain predetermined conditions.
  • The IF statement is written in the following syntax IF(logical_test, value_if_true, value_if_false), where: 
    • The first argument (logical_test) is the condition that needs to be tested and checked whether TRUE or FALSE.
    • The second argument (value_if_true) is the value to be returned if the condition is TRUE.
    • The third argument (value_if_false) is the value to be returned if the condition is FALSE.
  • It is an Excel built-in function that may also be used as a VBA function.
  • Its flexibility to be nested and used in conjunction with other worksheet operations makes it an indispensable tool for Excel developers.
  • The IF statement is helpful when creating scenarios for financial modeling, generating conditional formulas, or calculating debt or depreciation schedules in accounting.

Understanding the Excel IF Statement

It is a fundamental programming tool that provides a computer with the necessary intelligence to make judgments depending on the criteria specified by the user. It is an Excel built-in function that may also be used as a VBA function.

In Excel, the IF function evaluates a logical or mathematical expression and provides the appropriate response based on stated circumstances. 

The function examines the expression to determine whether a condition is satisfied and then returns a value based on the outcome.

It returns one predetermined value if the condition is true and another prepared value if the assertion is incorrect based on the criterion. Suppose statements are also known as IF THEN ELSE statements. The function is not case-sensitive.

The statement can be used in the following example to decide whether a sales assistant deserves a bonus. Referencing the example below, If sales surpass $, 5,000return a "Yes" for a Bonus; otherwise, return a "No".

Excel

The IF function may also be used to assess a single process or several IF functions in a single calculation.

Note

Financial analysts frequently use the IF function to assess and analyze data by examining various situations.

The function is capable of analyzing text, values, and even mistakes. It is not restricted to just comparing two objects and producing a single result. We may also employ mathematical operators and do various computations depending on our needs.

To make multiple comparisons, we may utilize nested IF statements to stack numerous IF functions together. The process may also be coupled with logical operations such as AND and OR to broaden the logical test and verify several conditions.

IF Function Formula

The syntax is as follows:

=IF(logical_test, value_if_true, value_if_false)

Where:

  • Logical_test (required) is the condition to be tested and evaluated as TRUE or FALSE, 
  • Value_if_true (optional) is the value returned if the condition is satisfied (TRUE), 
  • Value_if_false (optional) is the value returned if the condition is not satisfied (FALSE).Functions Argument

Note

While the second two arguments are optional, it is optimal to leave at most one of them blank to differentiate between whether the condition is true or false. If they are left blank, regardless of whether the state is TRUE or FALSE, the output will be 0.

In Excel, cells are referred to using either relative or absolute references. When the location of the cell changes, a formula with relative references changes.

Thus, when only one cell is used as a condition argument, you must remember to make the cell an absolute reference by locking it using the $ sign.

To construct the condition to be tested (first argument) in the IF function, the following logical comparison operators can be used:

Logical Operator
Logical Operator Meaning
= Equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
<> Not equal to

For example:

Assume Anna wants to check whether she’s older than her friend Ethan.

The following function will check whether the number in A2 (Anna’s age) is greater than the number in B2 (Ethan’s age) and will return “Older” if TRUE, that is, Anna’s age is more significant than Ethan’s age, and “Younger” if FALSE, that is Anna’s age is not greater than Ethan’s age.

=IF(A2>B2,"Older","Younger");

Excel Sheet

The IF Statement is similar to the IF-Else Statement in coding, and it gives the same results as the following sample syntax:

If a condition is TRUE, Then

  Code_if_TRUE will run

Else (If a condition is FALSE),

  Code_if_FALSE will run

End If

The code analyzes the criteria in the order they are listed. The standards are evaluated with a boolean value, which can be TRUE or FALSE. If the condition is TRUE, the relevant code will be performed, and no other states will be considered.

The keyword “Then” is a directive showing the program controls the instructions immediately after the IF formula.

Once the program evaluates the condition to TRUE, the statement is to be executed, and the code will stop. However, if condition_1 is evaluated to be false, the FALSE code will be performed instead.

The syntax's final line concludes with the code "End If." The code informs the software that this is the last line of the IF formula and that no more conditions need to be considered.

How to use the Excel IF Function

Now that you're familiar with the syntax of the IF function and the meaning of each argument let's look at examples and learn how to utilize If then statements in real-world applications.

The examples in this part are shown in the list below:

  • An example where the condition is either a positive or negative numbers
  • An example where the condition is a text with an Exact Match
  • An example where the condition is a text with a Partial Match
  • For example, where the condition is a date
  • An example where the condition is blank
  • For example, where the condition is another cell used for comparison
  • An example where the values to be returned are functions
  • Examples of using multiple IF functions simultaneously.

The functions in these examples cover most cases where the IF function can be used and applied to other scenarios with similar contexts.

Excel IF function with numbers as a condition

The function can check if the numbers provided are equal to, different than, greater than, or less than a certain threshold. 

The function will either return the value specified in the second argument if the condition is met or the value in the third argument.

Let’s take an example of a game where negative game scores are not permitted and must be changed. The following formula can be used to detect whether a cell contains a negative integer and will let the player know whether or not their entry is acceptable:

=IF(B2<0, "Invalid", "")

For negative game scores (less than 0), the formula will return the text "Invalid" notifying the player that their entry needs to be changed. In contrast, it will return a blank cell as a sign of admission acceptance for zeros and positive game scores.

Remarks

Excel IF function with text as a condition

An IF formula with the "equal to" or "not equal to" operator is generally used for text values.

For example, a retailer with a set of data needs to know whether or not to contact their supplier to check on the order's status.

The following formula checks the status of orders in B2 to see if an action is necessary:

=IF(B2="received", "", "Contact")

Action

In plain English, the formula states to return nothing if B2 equals "received" and to produce "Contact" otherwise.

Another method is to use the "not equal to" operator and swap the value if true and the value if false values:

=IF(B2<>"received", "Contact", "")

Note

Remember always to wrap text values in double quotes when using them as IF's arguments.

By default, IF, like most other Excel functions, is case-insensitive. It does not distinguish between "received," "Received," and "RECEIVED" in the above example. 

Use IF with the case-sensitive EXACT function to consider uppercase and lowercase letters as distinct characters.

For example, to return "No" only when B2 contains the word "received", use the following formula:

=IF(EXACT(B2,"received"), "", "Contact")

Sheet

Excel IF function for a partial match condition

When you base the condition on a partial match rather than an exact match, a simple way is to include wildcards in the logical test. This straightforward technique, however, will not work, as IF is not one of the functions that take wildcards.

Combining IF with ISNUMBER and SEARCH (case-insensitive) or FIND is a suitable strategy (case-sensitive). 

For instance, in the previous example, if no action is required for both "received" and "ordered" items, the following way will work flawlessly: 

=IF(ISNUMBER(SEARCH("received", B2)), "", "Contact")

Products

In this example, the formula will look for the word “received” in the cells, return nothing if it finds this word, and return “Contact” if it does not.

Only cells without mentioning the word “received” will show “Contact” in Action Required. In cell B5, where it says “Ordered/not received,” the formula finds “received” in the phrase and evaluates the condition as TRUE, returning a blank cell.

Excel IF function with dates

Suppose formulations for dates may appear identical to IF functions for numeric and text variables at first look. Unfortunately, that is not the case. 

Unlike many other functions, IF understands and interprets dates as simple text strings in logical tests. In other words, you cannot enter a date in the format "1/1/2022" or ">1/1/2022". 

The function recognizes dates that are wrapped using the DATEVALUE process. 

Let’s assume we want to check whether or not an exam has been completed by a certain date. For example, here's how to see if one date is more significant than another:

=IF(B2>DATEVALUE("12/15/2022"), "Coming soon", "Completed")

Table

This algorithm examines the dates in column B and returns "Coming soon" if an exam is scheduled for 15-Dec-2022 or later and "Completed" if it is designed earlier.

Of course, another way would be entering the target date in a specific cell and referring to it in the function. However, it would help if you remembered to make the cell an absolute reference by locking it using the $ sign. For example:

=IF(B2>$E$2, "Coming soon", "Completed")

It is possible to compare a date with today’s date using the TODAY() function. If the exam example was to determine if an exam has already been completed or not based on today’s date, the formula would look like this:

=IF(B2>TODAY(), "Coming soon", "Completed")

Excel IF function for blanks and non-blanks

If you want to mark your data based on whether a particular cell is empty or not, you may use the function in conjunction with ISBLANK or the logical expressions ="" (equal to blank) or <>"" (not similar to blank).

If a cell is visually empty, even if it includes a zero-length text, the equal to blank expression ="" evaluates to TRUE; otherwise, it evaluates to FALSE.

The <>"" expression evaluates to TRUE if a cell has data. Otherwise, it returns FALSE. Cells containing zero-length strings are regarded as empty.

Now, let's look at some blank and non-blank IF formulas in action. Assume you only have an entry date in column B if an exam has been completed. Use one of the following formulae to fill column C:

=IF(B2="", "", "Completed")

=IF(ISBLANK(B2), "", "Completed")

=IF($B2<>"", "Completed", "")

=IF(ISBLANK($B2)=FALSE, "Completed", "")

If none of the tested cells have zero-length strings, all of the formulae will provide the same results:

Data

IF statement to check if two cells are the same

To develop a formula that tests to see whether two cells are the same, compare the cells using the equals symbol (=) in the logical test of IF. 

As an example, let’s assume a basketball team with a certain number of matches played only wants to know whether or not there was a tie with other groups.

To check this, the following formula can be used:

=IF(B2=C2, "Tie", "")

Formula

Here, the function compares cells in column B to the corresponding cells in column C, returning the value “Tie” when the two cells match, indicating that the two scores are equal.

And it will return an empty or blank cell when the two cells are not equal, thus when there is no tie.

Note

In cases where a series of cells will be compared to one solitary cell, you must remember to make the cell an absolute reference by locking it using the $ sign.

IF-THEN formula to run another function

An Excel IF formula yielded values in all of the preceding scenarios. However, it may also do a computation or run another formula when a given condition is satisfied or not met. Insert another function or arithmetic expression inside the Value_if_true and Value_if_false parameters to do this.

Assuming that a school teacher wants to help students with grades less than or equal to 12 by giving them two bonus points. 

The following formula will check this condition and return the final grade with the bonus points if the condition is TRUE and without it if FALSE:

=IF(B2<=12, B2+2, B2)

If B2 is less than or equal to 12, we'll add two bonus points to the actual grade. Otherwise, nothing and the quality remain the same.

Numbers

Another possible solution is writing the threshold value in a cell and comparing it to the cells after making it an absolute reference.

Multiple Excel IF statements

In general, there are two approaches to writing numerous IF statements in Excel:

a. Nesting multiple IF statements one into another

Nested IF functions let you write numerous IF statements in the same cell, allowing you to test multiple conditions within one formula and return different values based on the findings.

Assume you are a professor and want to distribute bonus points based on the grades of your students and the following table.

Bonus Grades
Grades Range (/20) Bonus
Fewer than 11 0
11 to 14 0.5
15 to 18 1
Over 18 2

To complete this, construct three independent IF functions and nest them one within the other as follows:

=IF(B2>18, 2, IF(B2>=15, 1, IF(B2>=11, 0.5,0)))

Data

In this example, the function will check if the number in cell B2 is greater than 18 and will indicate that the student deserves two bonus points if it is. 

In cases where the grade in cell B2 is not greater than 18, it will run another IF function to check if it is greater or equal to 15 and indicate that the student should receive only one bonus point.

If the grade is not greater than 15, It will continue to run another function to check if it is greater than or equal to 11. If it is, the student will get 0.5 bonus points and nothing if not.

b. Using AND or OR function in the condition argument

To use AND or OR logic to assess several criteria, encapsulate the matching function in the logical test: 

AND - returns TRUE if all conditions are fulfilled.

OR - returns TRUE if any of the conditions are fulfilled.

Let’s take an example where a school administration needs to check whether or not students have passed their exams (scored higher than a 12).

The school first needs to know if students have passed both exams and then know if students have failed both exams.

An AND function can be used to check if students have passed both exams. It will return "Pass" if both scores in B2 and C2 are greater than or equal to 12 and “Fail” if not. 

The formula is:

=IF(AND(B2>=12, C2>=12), "Pass", "Fail")

Status

The school has two approaches to checking whether students have failed both exams. 

The first approach is also to use the AND function, but this time to check whether both grades are less than 12.

The formula is:

=IF(AND(B2<12, C2<12), "Fail", "Pass")

If the student did not fail their exams, the output would be “Pass” because the AND function requires both arguments to be true to output “Fail.”

The second approach is to use the OR function to check whether students have passed either one of the exams. The function will return “Pass” if a student passed one exam and failed the other and only return “Fail” if a student failed both exams.

To get a "Pass" if either score is higher than 12, the formula is as follows:

=IF(OR(B2>=12, C2>=12), "Pass", "Fail")

Sheet

Researched and authored by Wissam El Maouch | LinkedIn

Reviewed and edited by Divya Ananth | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources: