How to Use Excel IF Function Examples

Use the Excel IF function when you want to test something, and show one result if the test result is True, and a different result if the test result is False.

NOTE: In many cases, another Excel function would be more efficient than a complex IF formula. See the examples below.

Introduction

The Excel IF function is helpful if you want to check something on a worksheet, then show result A or B. This short video shows the steps for setting up a simple IF formula. There are written steps below.

Is Another Excel Function Better?

If you need to do anything more than a simple test, another Excel function might be better. For example:

Convert student grades from numbers to letters — use VLOOKUP or INDEX/MATCH
Choose an interest rate, based on transaction date — use HLOOKUP
Show a specific value instead of an error — use IFERROR (Excel 2007 and later)

How the Excel IF Function Works

There are 3 parts (arguments) to the IF function:

TEST something, such as the value in a cell.
Specify what should happen if the test result is TRUE .
Specify what should happen if the test result is FALSE .

if function checks for empty cell

Example 1: Check for Empty Cell

In this example, the Total in cell E7 should only show an amount if the Quantity has been entered in cell D7.

These are the 3 parts (arguments) to the IF function for this example:

TEST cell D7, to see if it is empty ( D7="" ),.
If D7 is empty (TRUE), the cell with the Total formula will show nothing ( "" ).
If D7 is not empty (FALSE), the cell with the Total formula will multiply Price x Quantity ( C7*D7 )

The completed formula is: =IF( D7=»» , «» , C7*D7 )

In the screen shot below, cell D7 is empty (TRUE), so the IF formula in cell E7 also looks empty.

When cell D7 is not empty (FALSE), the Total cell shows the value of Price x Quantity.

Example 2: Nested IF Functions

You can nest one or more IF functions within another IF function, to create more options for the results. In this example, the formula has two IF functions, to check both the Price and the Quantity cells

IF the Price cell is empty («»), the total will be empty («»)
Then, IF the Quantity cell is empty («»), the total will be empty («»)
Otherwise, multiply Price x Qty to calculate the Total

Notes on Nested IF Formulas

When building nested IF formulas, the order in which the tests are listed might be important. See the section below — the hardest tests must come first.
Some nested IF formulas can be replaced by a VLOOKUP formula, or INDEX and MATCH, for more flexibility. See the example of converting numeric grades to letter grades for students.

Formula in Named Excel Table

In this example, the orders are stored in a named Excel Table. As a result, there are structured references in the formula, that show the field names, instead of normal cell references.

For example, to calculate a simple total (Price x Quantity), this formula would be used: =[Price] * [Qty]

Check for Empty Cells

In Example 1, only the Quantity cell was checked in the IF formula. Here is that formula from Example 1, written with structured references:

To check both the Price and Quantity cells, another IF will be added to the existing formula:

If the Price cell is empty (TRUE), the cell with the Total formula will show nothing ("")
If the Qty cell is empty (TRUE), the cell with the Total formula will show nothing ("")
If neither cell is empty, the total is calculated ([Price]*[Qty])

The results for each possible scenario are shown below.

Order of Nested IF Functions

When nesting IF functions, be sure to put the hardest tests first, or the results could be incorrect.

Harder Test First

For example, in the following formula, there are two tests:

If the amount in cell B2 is 25000, the result would be "Good", because it is greater than 20000.

Easier Test First

However, if the tests are reversed, the formula would check for >=10000 first.:

Now, if the amount in cell B2 is 25000, the result of the formula would be "Average", because the results of that first test are TRUE.

Example 3: IF Function With Check Box

In this example, sales tax will be applied to local orders. On the order form, there is a check box that is linked to cell G11.

For local orders, click the check box, to add a check mark, and the linked cell shows TRUE
If the check mark is removed, the linked cell shows FALSE

The IF formula in cell E12 checks the linked cell (G11)l, to see if it contains "FALSE". If so, the tax amount is zero.

Интересно почитать: Как сделать диаграмму в excel по данным таблицы

If cell G11 does not contain "FALSE", the subtotal in cell E10 is multiplied by the Tax Rate in cell D12, to show the tax amount.

=IF(G11=FALSE,0,E10*D12)

Get the Sample File

To follow the examples in this tutorial, download the sample IF workbook. The zipped file is in xlsx format, and does not contain any macros.

Excel If Cell Contains Text

Excel If Cell Contains Text Then Formula helps you to return the output when a cell have any text or a specific text. You can check if a cell contains a some string or text and produce something in other cell. For Example you can check if a cell A1 contains text ‘example text’ and print Yes or No in Cell B1. Following are the example Formulas to check if Cell contains text then return some thing in a Cell.

If Cell Contains Text

Here are the Excel formulas to check if Cell contains specific text then return something. This will return if there is any string or any text in given Cell. We can use this simple approach to check if a cell contains text, specific text, string, any text using Excel If formula. We can use equals to operator(=) to compare the strings .

If Cell Contains Text Then TRUE

Following is the Excel formula to return True if a Cell contains Specif Text. You can check a cell if there is given string in the Cell and return True or False.

The formula will return true if it found the match, returns False of no match found.

If Cell Contains Partial Text

We can return Text If Cell Contains Partial Text. We use formula or VBA to Check Partial Text in a Cell.

Find for Case Sensitive Match:

We can check if a Cell Contains Partial Text then return something using Excel Formula. Following is a simple example to find the partial text in a given Cell. We can use if your want to make the criteria case sensitive.

Here, Find Function returns the finding position of the given string
Use Find function is Case Sensitive
IsError Function check if Find Function returns Error, that means, string not found

Search for Not Case Sensitive Match:

We can use Search function to check if Cell Contains Partial Text. Search function useful if you want to make the checking criteria Not Case Sensitive.

If Range of Cells Contains Text

We can check for the strings in a range of cells. Here is the formula to find If Range of Cells Contains Text. We can use Count If Formula to check the excel if range of cells contains specific text and return Text.

CountIf function counts the number of cells with given criteria
We can use If function to return the required Text
Formula displays the Text ‘Range Contains Text” if match found
Returns “Text Not Found in the Given Range” if match not found in the specified range

If Cells Contains Text From List

Below formulas returns text If Cells Contains Text from given List. You can use based on your requirement.

VlookUp to Check If Cell Contains Text from a List:
We can use VlookUp function to match the text in the Given list of Cells. And return the corresponding values.

Check if a List Contains Text:
=IF(ISERR(VLOOKUP(F1,A1:B21,2,FALSE)),”False:Not Contains”,”True: Text Found”)
Check if a List Contains Text and Return Corresponding Value:
=VLOOKUP(F1,A1:B21,2,FALSE)
Check if a List Contains Partial Text and Return its Value:
=VLOOKUP(“*”&F1&”*”,A1:B21,2,FALSE)

If Cell Contains Text Then Return a Value

We can return some value if cell contains some string. Here is the the the Excel formula to return a value if a Cell contains Text. You can check a cell if there is given string in the Cell and return some string or value in another column.

The formula will return true if it found the match, returns False of no match found. can

Excel if cell contains word then assign value

You can replace any word in the following formula to check if cell contains word then assign value.

Search function will check for a given word in the required cell and return it’s position. We can use If function to check if the value is greater than 0 and assign a given value (example: 1) in the cell. search function returns #Value if there is no match found in the cell, we can handle this using IFERROR function.

Count If Cell Contains Text

We can check If Cell Contains Text Then COUNT. Here is the Excel formula to Count if a Cell contains Text. You can count the number of cells containing specific text.

The formula will Sum the values in Column B if the cells of Column A contains the given text.

Интересно почитать: Как в excel перевести число в тысячи

Count If Cell Contains Partial Text

We can count the cells based on partial match criteria. The following Excel formula Counts if a Cell contains Partial Text.

We can use the CountIf Function to Count the Cells if they contains given String
Wild-card operators helps to make the CountIf to check for the Partial String
Put Your Text between two asterisk symbols (*YourText*) to make the criteria to find any where in the given Cell
Add Asterisk symbol at end of your text (YourText*) to make the criteria to find your text beginning of given Cell
Place Asterisk symbol at beginning of your text (*YourText) to make the criteria to find your text end of given Cell

If Cell contains text from list then return value

Here is the Excel Formula to check if cell contains text from list then return value. We can use COUNTIF and OR function to check the array of values in a Cell and return the given Value. Here is the formula to check the list in range D2:D5 and check in Cell A2 and return value in B2.

If Cell Contains Text Then SUM

Following is the Excel formula to Sum if a Cell contains Text. You can total the cell values if there is given string in the Cell. Here is the example to sum the column B values based on the values in another Column.

The formula will Sum the values in Column B if the cells of Column A contains the given text.

Sum If Cell Contains Partial Text

Use SumIfs function to Sum the cells based on partial match criteria. The following Excel formula Sums the Values if a Cell contains Partial Text.

SUMIFS Function will Sum the Given Sum Range
We can specify the Criteria Range, and wild-card expression to check for the Partial text
Put Your Text between two asterisk symbols (*YourText*) to Sum the Cells if the criteria to find any where in the given Cell
Add Asterisk symbol at end of your text (YourText*) to Sum the Cells if the criteria to find your text beginning of given Cell
Place Asterisk symbol at beginning of your text (*YourText) to Sum the Cells if criteria to find your text end of given Cell

VBA to check if Cell Contains Text

Here is the VBA function to find If Cells Contains Text using Excel VBA Macros.

If Cell Contains Partial Text VBA

We can use VBA to check if Cell Contains Text and Return Value. Here is the simple VBA code match the partial text. Excel VBA if Cell contains partial text macros helps you to use in your procedures and functions.

CheckIfCellContainsPartialText VBA Function returns true if Cell Contains Partial Text
inStr Function will return the Match Position in the given string

If Cell Contains Text Then VBA MsgBox

Here is the simple VBA code to display message box if cell contains text. We can use inStr Function to search for the given string. And show the required message to the user.

inStr Function will return the Match Position in the given string
blnMatch is the Boolean variable becomes True when match string
You can display the message to the user if a Range Contains Text

Which function returns true if cell a1 contains text?

You can use the Excel If function and Find function to return TRUE if Cell A1 Contains Text. Here is the formula to return True.

Which function returns true if cell a1 contains text value?

You can use the Excel If function with Find function to return TRUE if a Cell A1 Contains Text Value. Below is the formula to return True based on the text value.

Share This Story, Choose Your Platform!

Excel Formula If Cell Contains Text Then Return Value in Another Cell

Display Cell Contents in Another Cell in Excel

2 Comments

Hi Sir,Thank you for the great explanation, covers everything and helps use create formulas if cell contains text values.

Many thanks! Meghana!!

Perfect! Very Simple and Clear explanation. Thanks!!

If excel как пользоваться

The IF function is one of the most used functions in Excel. This page contains many easy to follow IF examples.

Simple If Examples

The IF function checks whether a condition is met, and returns one value if true and another value if false.

1a. For example, take a look at the IF function in cell B2 below.

Explanation: if the price is greater than 500, the IF function returns High, else it returns Low.

1b. The following IF function produces the exact same result.

Note: you can use the following comparison operators: = (equal to), > (greater than), = (greater than or equal to), (not equal to).

2. Always enclose text in double quotation marks.

3a. The formula below calculates the progress between two points in time.

3b. You can use the IF function to display an empty string if the end value hasn’t been entered yet (see row 5).

Explanation: if the end value is not empty (<> means not equal to), the IF function calculates the progress between the start and end value, else it displays an empty string (two double quotes with nothing in between).

And/Or Criteria

Use the IF function in combination with the AND function and the OR function and become an Excel expert.

1. For example, take a look at the IF function in cell D2 below.

Explanation: the AND function returns TRUE if the first score is greater than or equal to 60 and the second score is greater than or equal to 90, else it returns FALSE. If TRUE, the IF function returns Pass, if FALSE, the IF function returns Fail.

2. For example, take a look at the IF function in cell D2 below.

Explanation: the OR function returns TRUE if at least one score is greater than or equal to 60, else it returns FALSE. If TRUE, the IF function returns Pass, if FALSE, the IF function returns Fail.

3. For example, take a look at the IF function in cell D2 below.

Explanation: the AND function above has two arguments separated by a comma (Table, Green or Blue). The AND function returns TRUE if Product equals «Table» and Color equals «Green» or «Blue». If TRUE, the IF function reduces the price by 50%, if FALSE, the IF function reduces the price by 10%.

Nested If

The IF function in Excel can be nested, when you have multiple conditions to meet. The FALSE value is being replaced by another IF function to make a further test.

1. For example, take a look at the nested IF formula in cell C2 below.

Explanation: if the score equals 1, the nested IF formula returns Bad, if the score equals 2, the nested IF formula returns Good, if the score equals 3, the nested IF formula returns Excellent, else it returns Not Valid. If you have Excel 2016 or later, simply use the IFS function.

2. For example, take a look at the nested IF formula in cell C2 below.

Explanation: if the score is less than 60, the nested IF formula returns F, if the score is greater than or equal to 60 and less than 70, the formula returns D, if the score is greater than or equal to 70 and less than 80, the formula returns C, if the score is greater than or equal to 80 and less than 90, the formula returns B, else it returns A.

More about If

The IF function is a great function. Let’s take a look at a few more cool examples.

1. For example, use IF and AND to test if a value is between two numbers.

Explanation: the AND function returns TRUE if the person is older than 12 and younger than 20, else it returns FALSE. If TRUE, the IF function returns Yes, if FALSE, the IF function returns No.

2. You can combine IF with AVERAGE, SUM and other Excel functions. The sky is the limit!

Explanation: the AND function returns TRUE if the input value is greater than 100 and the average of the values in the named range Data1 is greater than 100, else it returns FALSE. If TRUE, the IF function returns the sum of Data2, if FALSE, the IF function returns 0.

Confused? You can always use the Evaluate Formula tool to step through your IF formula. This Excel tool helps you understand a formula.

3. For example, select cell G3 above.

4. On the Formulas tab, in the Formula Auditing group, click Evaluate Formula.

5. Click Evaluate multiple times.

Note: try it yourself. Download the Excel file and choose one of the IF formulas explained on this page. You can use this tool to step through all kinds of formulas.