Skip to Content

How to use IF CONTAINS in Excel (2 Quickest Methods of 2024)

Posted on Last updated:

Excel is a powerful spreadsheet application for statistical and conditional data analysis. You can easily check if a cell contains a specific text string and display the desired output. It can be done using a combination of the IF, OR, ISNUMBER, and SEARCH functions.

Such a combination searches for a text string within a cell and returns a desired output when the condition is met. 

In this article, let us learn to use the IF Contains in Excel. We will dive deep into the details and discuss various applications of the IF, OR, ISNUMBER, and SEARCH functions. We will also learn to test cell content against multiple text strings, so make sure to read the article till the end.

Let’s start with each function and its general syntax.

Explained: The IF Function in Excel

The IF is a conditional or comparison function whose output depends upon the logical expression. It is a powerful function in Excel, which, when combined with other functions, creates advanced formulas and obtains complicated results.

The General Syntax for the IF function is as follows,

=IF(logical_test, [value_if_true], [value_if_false])

Each argument of the above function needs to be replaced with the proper information.

  • “logical_test” – This is the condition or criteria that need to be satisfied by the given cell.  The output will be either TRUE or FALSE, which will be used to trigger the next arguments of the function. You can use mathematical and logical operators like greater than (>), less than (<), and equal to (=) to test the cell’s contents.
  • “value_if_true” – It is used to specify the desired output if the specific condition is satisfied.
  • “value_if_false” –  Here, you can define the text string that you wish the IF function to return if the criteria are not satisfied.

The working of the 3rd argument is similar to the 2nd one, but it is an optional argument. If you don’t define it, then the function will simply return a blank value.

Note that the “logical_test” argument uses various operators to define criteria or conditions. However, in the case of this article, we won’t use any of those operators but instead leverage other functions in Excel to define the criteria or conditions.

Explained: The SEARCH Function in Excel

The SEARCH function in Excel is used to search for a particular text string. It can also be used to find the location of the substring within a text string if found. 

Here, we will use it as a conditional formula to return,

  • An integer value when certain criteria have been satisfied
  • A “#VALUE!” error when certain criteria are not satisfied

The general syntax for the function is as follows,

=SEARCH(find_text,within_text,[start_num])

Let’s quickly discuss each argument of the SEARCH function,

  • “find_text” – This argument needs to be replaced by the substring or character you want to search within a text string. You can manually enter the string or mention the cell reference holding the same. Note that if you choose to enter the value manually, then make sure to use the double quotation marks.
  • “within_text” – Here, you need to specify the cell reference holding the text string where we wish to find the substring or character.
  • “start_num” – This is an optional argument that defines the position from where the function should start the search. Suppose we put “2” as the input value here; then, the function will begin searching for a specific substring from the second character of the main string. If not defined, then the function takes the default value as “1” and starts testing the substring against the main string from the first character.

The SEARCH function is pretty straightforward to use.

In this article, we will use the first two arguments of the formula. Instead of using the actual integer number obtained using this formula, we will just treat it as the condition that has been met.

Explained: The ISNUMBER Function in Excel

The ISNUMBER function in Excel checks if a cell contains a numerical value or not. This can also check if a cell contains specific text as part of the content.

The General syntax of the ISNUMBER function is as follows,

=ISNUMBER(value)

Where the “value” argument is used to check if a cell contains a number. It can be a number, date, function, or any expression or the cell reference holding the value.

It returns a TRUE if the value is a number and FALSE if it is not.

This function is very simple, with just a single argument.

By using ISNUMBER in conjunction with SEARCH, you can effectively create conditional statements in Excel to check the presence of specific text strings within cells.

Explained: The OR Function in Excel

The OR function in Excel returns TRUE or FALSE based on two or more conditions. It is often used together with the IF function.

The general syntax for the OR function is as follows,

=OR(logical1,[logical2],...)

where logical argument represents the logical expressions or conditions you want to test. The output will be either TRUE or FALSE.

Note that the first logical expression is mandatory, the second one is optional, and so on.

Download the Example Excel Sheet

Before we jump into the actual steps to create an IF Contains formula in Excel, click on the following link to download the spreadsheet containing the datasets we used in the upcoming sections.

Click Here To Download!

Practice is the key to success, so make sure to follow me as I take you through this article. It will surely strengthen your knowledge of the IF and other functions discussed in this article.

It is okay if you have your own Excel sheet ready with the required dataset; you can skip downloading the above file.

How to check if a cell contains a specific text string using the IF + SEARCH Function

Let us use the combination of the IF and SEARCH functions in Excel to find a text string within multiple cells of the column.

Consider the following table, which has employee details, including their name and department.

Our task is to find the employees from the IT department.

We have added the third column titled “IF + SEARCH” to get the results. If someone is working in the IT department, we will mark the respective employee with the “Yes” keyword in the third column.

Here are the steps,

  • Click on the cell “C2”
  • Type “=IF”
  • Select the first option from the popup or press “Tab” on your keyboard
  • Now, in the place of the “logical_test” argument, type “SEARCH” on your keyboard
    (As SEARCH is a function in Excel, a popup will be displayed, as shown in the above image)
  • Select the first option or press the “Tab” key
  • Next, the SEARCH function will ask you to replace the “find_text” argument with proper information
  • Type “IT”
    (Make sure you use the double quotation marks)
  • Press “,” to move to the next argument within the SEARCH function
  • Provide the cell reference as “B2”
  • Complete the bracket using the “)” on your keyboard
  • Press “,” key
  • Now, you will taken back to the IF function
  • Replace the “value_if_true” argument with the “Yes”. It will display the keyword “Yes” if the SEARCH function would find the “IT” within the cell B2
  • The “value_if_false” argument is optional. So, we won’t go for it
    (Note that another reason is that the IF function will return a “#VALUE!” error regardless of any value at the “value_if_false” argument. It is due to the SEARCH function, which returns a “#VALUE!” error when it is not able to find the text string within the cell)
  • Complete the brackets using “)”
  • Press “Enter”

Our final formula using the SEARCH function is as follows,

=IF(SEARCH("IT",B2),"Yes")

To apply this formula for the rest of the cells, hover to the lower-rightmost corner of the cell “C2,” and you will notice a Fill Handle icon. Click on it and drag it to the end of the table.

Here’s the output,

How To Use IF Contains In Excel - Find Specific Text String

How to check if a cell contains a specific text string using the IF + ISNUMBER + SEARCH 

The combination of IF, ISNUMBER, and SEARCH functions allows you to determine if a specific substring exists within a cell and return a corresponding value based on the result.

We are considering the same employee data as the previous example. Our task is to find the employees working in the IT department.

So, let’s get started.

  • Select the cell “C2”
  • Type “=if”
  • Select the first option from the popup or press “Tab” on your keyboard
  • Now, replace the logical_expression argument of the IF function with “ISNUMBER”
    (As the ISNUMBER is also a function in Excel, you will see a popup as shown below)
  • Now, type “SEARCH” and select the first popup as shown below
  • In place of the “find_text” argument, type in “IT”
    (Make sure you use the double quotation marks; otherwise, the formula won’t work as expected at the end)
  • Press “,” to move to the next argument
  • Enter the cell reference as “B2”
  • Complete the SEARCH function with a closing parenthesis “)”
  • Also, finish the ISNUMBER formula with a closing bracket “)”
  • In place of the “[value_if_true]” argument, type “Yes”
  • Type “,” to enter the next argument
  • In place of the “[value_if_false]” argument, type “No”
  • End the formula with a closing parenthesis “)”
  • Press “Enter”

Our final formula using the SEARCH function is as follows,

=IF(ISNUMBER(SEARCH("IT",B2)), "Yes",”No”)

To apply the above formula for the rest of the cells, use the autofill feature as shown in the above GIF.

You need to select the cell “C2” and hover to the lower-rightmost corner until you see the Fill Handle. Once you see a “+” icon, click on it and drag it to the end of the table.

How to test the cell content against multiple text strings

In the previous sections, we learned to test the cell content against a single condition or criteria.

However, there may be a few instances when we need to test the cell content against multiple criteria.

In that case, use the combination of the OR, IF, ISNUMBER, and SEARCH functions. It creates a powerful formula that helps you test the cell content against multiple conditions.

Such complex formulas are used for advanced analysis.

Consider the following dataset with employee names and departments. Our task is to find the employees from the IT and Sales departments.

Let’s begin,

  • Click on the cell “C2”
  • Type “=IF”
  • Select the first option or press “Tab” on your keyboard
  • Next, type “OR”
    (You will see the following popup as the OR is a function in Excel)
  • Press the “Tab” key to select the OR function
  • Type “ISNUMBER”
  • Press the “Tab” key to select it
    (As ISNUMBER is a function in Excel, a popup will be displayed, as shown in the above image)
  • Now, type in “SEARCH” and press the “Tab” key
  • In place of the “find_text” argument, enter “IT” and make sure to use the double quotation marks
    (IT is the first text string that is being searched for)
  • Press “,”  to move to the next argument
  • Provide the cell reference as “B2”
  • Complete the SEARCH formula with the “)” on your keyboard
  • Now end the ISNUMBER formula with a closing parenthesis “)”
  • Press “,” key
  • Type “ISNUMBER” and press “Tab”
  • Now type “SEARCH” and press the “Tab” key on your keyboard to select the function
  • In place of the “find_text” argument, enter “Sales” and ensure using double quotation marks
    (Sales is the second text string that is being searched for)
  • Press “,” on your keyboard to move to the next argument
  • Provide the cell reference as “B2”
  • End the SEARCH formula with a closing brackets “)”
  • Similarly, complete the ISNUMBER and OR functions with two closing parenthesis “))”
  • Press “,” to move to the next argument
  • In place of “[value_if_true]”, enter “Yes”
  • Enter “No” in place of the “[value_if_false]” argument
  • Complete the brackets using “)”
  • Press “Enter”

Here’s how the final formula looks,

=IF(OR(ISNUMBER(SEARCH("IT", B2)),ISNUMBER(SEARCH("Sales", B2))), "Yes", "No")

By following these steps, you can effectively check if a cell contains the multiple text strings “IT” and “Sales” and return “Yes” or “No” based on the result.

To Summarize: IF Contains In Excel

There is no built-in IF CONTAINS function or formula in Excel. You need to combine the IF function with the ISNUMBER, OR, and SEARCH functions to find a text string within the cell.

I hope you learned all the bells and whistles of IF Contains Excel formulas.

Let us know in the comment below if you are stuck somewhere or having any particular error while finding a text string using the methods discussed above.

Also, make sure to explore our blog section for more tips and tricks on how to use Microsoft Excel and Google Sheets like a pro.