How to Use “If Contains” Formula in Excel

Using the "If Contains" formula in Excel helps you perform specific actions or calculations if a particular text or value is present within a cell. The formula combines the IF and SEARCH functions to achieve this. The IF function tests a condition and returns one value if the condition is true and another value if the condition is false. The SEARCH function looks for a specific text within a cell and returns its position if found.

Here's a step-by-step guide on how to use the "If Contains" formula in Excel:

1. Open your Excel workbook and navigate to the sheet where you want to apply the formula.
2. Identify the cell in which you want to display the result of the "If Contains" formula.
3. Click on the cell and type the following formula:

=IF(ISNUMBER(SEARCH("text_to_find", source_cell)), value_if_true, value_if_false)

Replace the following parts of the formula with your specific information:

• "text_to_find": Replace this with the text or value you want to search for within the source cell.
• source_cell: Replace this with the cell reference where you want to search for the specified text or value.
• value_if_true: Replace this with the value or action you want to perform if the specified text or value is found in the source cell.
• value_if_false: Replace this with the value or action you want to perform if the specified text or value is not found in the source cell.

4. Press Enter to execute the formula. The result will be displayed in the selected cell.

Example

Suppose you have a list of products in column A (A2:A10), and you want to check if the word "Laptop" is present in each product name. If it is, you want to apply a 10% discount (in column B), otherwise, no discount.

Here's how you can use the "If Contains" formula in this scenario:

1. Click on cell B2, where you want to display the discount percentage.
2. Type the following formula:

=IF(ISNUMBER(SEARCH("Laptop", A2)), 10%, 0%)

3. Press Enter to execute the formula. You will see a 10% discount if the product in cell A2 contains the word "Laptop", otherwise 0%.
4. To apply this formula to the entire product list, click on cell B2, and drag the fill handle (the small square at the bottom-right corner of the selected cell) down to cell B10. This will copy the formula to the other cells, adjusting the cell references accordingly.

Now, you have successfully used the "If Contains" formula in Excel to apply discounts based on the product name.