## If cell contains text from list

The array formula in cell C3 checks if cell B3 contains at least one of the values in List (E3:E7), it returns "Yes" if any of the values are found in column B and returns nothing if cell contains none of the values.

Example, cell B3 contains XBF which is found in cell E7. Cell B4 contains text ZDS found in cell E6. Cell C5 contains no values in list.

The COUNTIF function has ampersands concatenated to cell range E3:E7 which makes it count the cell if it contains a value in the list.

becomes

and returns this array

which tells us that the last value in list is found in cell B3. The OR function returns TRUE if at least one of the values in the array is 1.

returns TRUE.

The IF function then returns "Yes" if logical test is TRUE and nothing if logical test is FALSE.

returns "Yes" in cell B3.

To enter an array formula press and hold CTRL + SHIFT simultaneously, then press Enter once. Release all keys.

The formula bar now shows the formula enclosed with curly brackets telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

### Alternative formula

The following formula is quite similar to the formula above except that it is a regular formula and it has an additional INDEX function.

### Download excel *.xlsx

Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

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

If cell contains multiple values

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

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 [โฆ]

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 [โฆ]

### 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