## If cell contains text from list

*Article last updated on March 25, 2018*

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

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

Find latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with […]

Extract unique distinct values from a multi-column cell range

Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]

Calculate average of last 10 data with possible blank cells

Question: Answer: This array formula creates a dynamic range, filtering the 10 last data. Adjust cell ranges $A$1:$A$25 in formula below. […]

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 If […]

If cell equals value from list

The array formula in cell C3 checks if cell B3 is equal to any of the values in E3:E5, the […]

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

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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form