Excel Formulas to Check If Cell Contains One of Many Things!!

If you want to test a cell to see if it contains one of several things, you can do so with a formula that uses the SEARCH function, with help from the ISNUMBER and SUMPRODUCT functions. Here, we will show the formulas to check if the cell contains one of many things in Excel. Let’s jump into this article!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

Generic Formula:

  • Use the below formula to check if the cell contains all the listed items in Excel.

=SUMPRODUCT(–ISNUMBER(SEARCH(things,A1)))>0

Syntax Explanations:

  • SUMPRODUCT – This function helps to multiply the corresponding arrays or ranges and returns the sum of the products. Read more on the SUMPRODUCT Function.
  • ISNUMBER – The ISNUMBER Function returns TRUE when a cell contains a number, and FALSE if there will not be a number.
  • SEARCH – In Excel, the SEARCH function locates the character between two text strings and returns to the number of the starting position of the first text string from the first character of the second text string.
  • A1 – It represents the input text strings.
  • Minus Operator (-) – This symbol will help to subtract any two values.
  • Comma symbol (,) – It is a separator that helps to separate a list of values.
  • Parenthesis () – The main purpose of this symbol is to group the elements.

Practical Example:

Refer to the below example image.

  • First, we will enter the input text strings in Column B.
  • Now, we are going to check if the cell contains one of many things listed in Column E.
  • Select any cell and apply the above-given formula.
  • Finally, press ENTER to get the result out.

Wrap-Up:

From this chapter, you can get some clarification on how to check if the cell contains one of many things in Excel. Leave a comment or reply below to let me know what you think!

Thank you so much for visiting *Geek Excel!! **If you want to learn more helpful formulas, check out Excel Formulas *!! **

Read Ahead:

33