If cell contains multiple values
This article demonstrates formulas that perform a partial match for a given cell using multiple strings specified in cells F2 and F3.
Table of Contents
1. Find cells containing all conditions
The array formula in cell C3 checks if the value in cell B3 contains all conditions specified in cells F2:F3, it returns a boolean value TRUE or FALSE.
Both conditions must be found in cell C3 in order to return TRUE. For example, cell B4 contains one of the two conditions, however, the formula returns FALSE in cell C4.
1.1 How to enter an array formula
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.
1.2 Explaining formula in cell C3
Step 1 - Find cells containing at least one condition
The SEARCH function returns the character position of a substring in a text string, it returns an error if not found.
SEARCH(find_text,within_text, [start_num])
SEARCH($F$2:$F$3,B3)
becomes
SEARCH({"D";"U"},"A, U, G, E")
and returns {#VALUE!;4}.
Step 2 - Count numbers in array
The COUNT function counts the number of values that contain a number, it conveniently also ignores errors.
COUNT(value1, [value2], ...)
COUNT(SEARCH($F$2:$F$3,B3))
becomes
COUNT({#VALUE!; 4})
and returns 1. Only one value in the array is a number.
Step 2 - Count numbers in the array
Lastly, the equal sign compares the output with number 2. There are two conditions specified in cells F2 and F3, this is why the count is compared to two.
COUNT(SEARCH($F$2:$F$3,B3))=2
becomes
1=2
and returns FALSE in cell C3.
2. Find cells containing at least one condition
If you want the formula to return TRUE if at least one value is found change the array formula to:
The formula above is almost identical to the formula in section 1, however, there are two comparison operators in this formula instead of one.
The equal sign and the larger than sign combined lets you check if a value is equal to or larger than a given condition. Read section 1.2 for a more detailed explanation.
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:
3. Find cells containing all conditions (regular formula)
This formula is slightly larger but has the advantage of being a regular formula, no need to enter the formula as an array formula.
Explaining formula in cell C3
Step 1 - Append asterisks to each condition
The ampersand character lets you concatenate strings in an Excel formula. The asterisk character is a wildcard character that matches 0 (zero) to any length of characters.
The part shown below appends asterisks to the start and end of each string in cells F2 and F3.
"*"&$F$2:$F$3&"*"
returns
{"*D*"; "*U*"}.
Step 2 - Count values using partial match
The COUNTIF function calculates the number of cells that is equal to a condition.
COUNTIF(range, criteria)
COUNTIF(B3,"*"&$F$2:$F$3&"*")
becomes
COUNTIF(B3, {"*D*"; "*U*"})
becomes
COUNTIF("A, U, G, E", {"*D*"; "*U*"})
and returns {0; 1}.
Step 3 - Multiply by 1
This step is required to convert the array formula to a regular formula, this step is not needed if you are using Excel 365.
COUNTIF(B3,"*"&$F$2:$F$3&"*")*1
becomes
{0; 1}*1
and returns {0; 1}.
Step 4 - Add numbers in array and return a total
The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.
SUMPRODUCT(array1, [array2], ...)
SUMPRODUCT(COUNTIF(B3,"*"&$F$2:$F$3&"*")*1)
becomes
SUMPRODUCT({0; 1})
and returns 1.
Step 5 - Compare the result to two
The equal sign compare the result to two, this makes the formula return TRUE if both string are found. Change this value if you have more or fewer conditions.
SUMPRODUCT(COUNTIF(B3,"*"&$F$2:$F$3&"*")*1)=2
becomes
1=2
and returns FALSE in cell C3.
Get Excel *.xlsx file
If cell contains multiple values.xlsx
Related articles
This post explains how to lookup a value and return multiple values. No array formula required.
This article demonstrates several ways to check if a cell contains any value based on a list. The first example […]
The image above demonstrates a formula that matches a value to multiple conditions, if the condition is met the formula […]
The AND function allows you to have multiple conditions in an IF function, you can have up to 254 arguments. […]
Nested IF statements in a formula are multiple combined IF functions so more conditions and outcomes become possible. They all are […]
This article demonstrates formulas that check if a cell value is equal to any value in a given list. Table […]
This article demonstrates different formulas based on if a cell contains a given text. Formula in cell C3: =B3=$E$3 The […]
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 […]
3 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.
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.
Hello, thank you very much. But my problem is the formula does not separate a double digit value from a single digit value.
So if cell B6 contains a values like (X,Q,DF,UJ) the formula still returns true.
Please can you help me make the formula differentiate between multiple digit value and single digit values?
Thank you again.