## If cell contains multiple values

*Article last updated on March 25, 2018*

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

Create dependent drop down lists containing unique distinct values

This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

I read an interesting blog post Is A Particular Word Contained In A Text String? on Spreadsheetpage. That inspired me […]

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

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

Table of contents Initials from first and last names Initials from first, middle and last names Create middle name initials […]

How to use the SEARCH function

Returns a number representing the position a substring is, if found in a textstring. FIND is case sensitive and SEARCH is not.

Sum values containing text based on a condition

Question: I want to sum cells that have a "C" and a decimal number. The cells have other numbers and […]

Formula for matching a date within a date range

Table of contents Match a date when a date range is entered in a single cell Match a date when […]

Identify overlapping date ranges

The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]

Overview This article describes how to create a random playlist of four teams total. Column A contains four teams. Each […]

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