## If cell contains multiple values

The array formula in cell C3 checks if text string in B3 contains all values in F2:F3.

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

### Explaining formula in cell C3

The SEARCH function returns the character position of a substring in a text string, it returns an error if not found.

SEARCH($F$2:$F$3,B3)

becomes

SEARCH({"D";"U"},"A, U, G, E")

and returns {#VALUE!;4}.

THE COUNT function counts the number of values that contain a number, it conveniently also ignores errors.

COUNT(SEARCH($F$2:$F$3,B3))

becomes

COUNT({#VALUE!;4}) and returns 1. Only one value in the array is a number.

Lastly, the equal sign compares the output with number 2. COUNT(SEARCH($F$2:$F$3,B3))=2

becomes

1=2 and returns FALSE in cell C3.

### Modify formula to check if at least one value is found

If you want the formula to return TRUE if at least one value is found change the formula to:

The possibilities are endless here if you want the formula to return TRUE if at least 2 out of 3 values are found, change the formula to:

### Regular formula

This formula is slightly larger but has an advantage of being a regular formula.

### Download Excel *.xlsx file

If cell contains text from list

This article demonstrates several ways to check if a cell contains a value based on a list. The first example […]

Nested IF statements in a formula are multiple combined IF functions so more conditions and outcomes become possible. They all are […]

IF with AND function – multiple conditions

The AND function allows you to have multiple conditions in an IF function, you can have up to 254 arguments. […]

If cell equals value from list

Regular formula Formula in cell C3: =IF(COUNTIF($E$3:$E$5,B3),"Yes","No") The COUNTIF function counts how many values in E3:E5 match cell B3, it returns […]

The picture above shows different values in column B and a formula in column C that tries to identifies the […]

The easiest way to check if a cell has a value is, in my opinion, to use the equal sign […]

The easiest way to check if a cell contains a specific text string is, in my opinion, the IF and […]

Use IF + COUNTIF to perform numerous conditions

The COUNTIF function allows you to construct a small IF formula that carries out plenty of logical expressions. Combining the IF […]

### 2 Responses to “If cell contains multiple values”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

Here is another normally-entered formula that can be used to check if the F2 and F3 values are both in the cell in Column B...

=COUNTIFS(B3,"*"&F$2&"*",B3,"*"&F$3&"*")=1

Rick Rothstein,

I never gave the COUNTIFS function a thought, thank you for commenting.