How to Check If Cell Contains One of Several Values in Excel?

If we have a dataset in which we need to check if there are any cell values or text which is not required or inappropriate, then the same can be identified in seconds using an excel function. There are many functions through which we can identify specific set of text in cell values of the complete dataset. Let’s have a look at the formulas and example and learn how to implement the same in a dataset.

  • Check if dataset contains one or more values from a specific list

Check if dataset contains one or more values from a specific list

Step 1 − A sample data has been created as shown below.


Step 2 − Enter the following formula in a separate column, where you want the result to appear.

=SUMPRODUCT(--ISNUMBER(SEARCH(find_text_range, within_text))), “Yes”,”No”)


Step 3 − Now drag the cell to copy the formula in other cells also to view the output against all cell


Formula Syntax Description

Argument Description
SEARCH (find_text, within_text, [start_num])

  • This function search for a specific text/character in the selected text and returns the location of identified text.

  • Find_text specifies the range or specific text that need to be searched in a cell or range.

  • Within_text specifies the range of cell where the selected text to be searched.

  • Start_num specifies the starting position of search in within_text. It is an optional parameter.
--ISNUMBER(value)

  • This function returns True of the selected cell value is a number and False if anything else.

  • The (--) operators converts the Boolean values to numbers. The first - converts TRUE to -1, and the second one changes it to 1.

  • Similarly, the first - converts FALSE to 0, and the second one doesn't change it).

  • Value is the cell value that needs to be checked.
SUMPRODUCT(expression, range)

  • This function multiplies ranges or arrays together and returns the sum of products.

  • Expression specifies the first array of range to be multiplied and added.

  • Range specifies another array that to be added in the first one.
IF(logical_test, {value_if_true},{value_if_false}

  • Logical_test specifies the condition basis which the data needs to be rendered.

  • Value_if_true specifies the value that shall be returned if the condition satisfies.

  • Value_if_false specifies the value that shall be returned if the condition does not satisfy

Conclusion

However, there are other combination formulas are also available for doing the same task. Some of the formulas are as following −

=TEXTJOIN(", ", TRUE, IF(COUNTIF(<where to find cell>, "*"&<What to find range>&"*"), <What to find range>, ""))

This formula is applicable for excel 2019 and above version. Though there are many methods to search specific text in the dataset but the one explained in this article is applicable to all versions of excel. Keep learning excel and keep practicing.

Updated on: 16-Sep-2022

3K+ Views

Related Articles

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements