Excel if cell does not contain multiple values

Author: Oscar Cronquist Article last updated on January 01, 2022

Table of Contents Show

1.1 How to enter an array formula
1.2 Explaining formula in cell C3
2. Find cells containing at least one condition
3. Find cells containing all conditions (regular formula)
Explaining formula in cell C3
Get Excel *.xlsx file

This article demonstrates formulas that perform a partial match for a given cell using multiple strings specified in cells F2 and F3.

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.

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

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:

=COUNT(SEARCH($F$2:$F$3,B3))=>1

How to enter an array formula

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:

=COUNT(SEARCH($F$2:$F$4,B3))=>2

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.

=SUMPRODUCT(COUNTIF(B3,"*"&$F$2:$F$3&"*")*1)=2

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

Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 More...Less

If the data you want to filter requires complex criteria (such as Type = "Produce" OR Salesperson = "Davolio"), you can use the Advanced Filter dialog box.

To open the Advanced Filter dialog box, click Data > Advanced.

The Advanced command works differently from the Filter command in several important ways.

It displays the Advanced Filter dialog box instead of the AutoFilter menu.
You type the advanced criteria in a separate criteria range on the worksheet and above the range of cells or table that you want to filter. Microsoft Office Excel uses the separate criteria range in the Advanced Filter dialog box as the source for the advanced criteria.

The following sample data is used for all procedures in this article.

The data includes four blank rows above the list range that will be used as a criteria range (A1:C4) and a list range (A6:C10). The criteria range has column labels and includes at least one blank row between the criteria values and the list range.

To work with this data, select it in the following table, copy it, and then paste it in cell A1 of a new Excel worksheet.

Type	Salesperson	Sales




Type	Salesperson	Sales
Beverages	Suyama	$5122
Meat	Davolio	$450
produce	Buchanan	$6328
Produce	Davolio	$6544

You can compare two values by using the following operators. When two values are compared by using these operators, the result is a logical value—either TRUE or FALSE.

Comparison operator	Meaning	Example
= (equal sign)	Equal to	A1=B1
> (greater than sign)	Greater than	A1>B1
< (less than sign)	Less than	A1<B1
>= (greater than or equal to sign)	Greater than or equal to	A1>=B1
<= (less than or equal to sign)	Less than or equal to	A1<=B1
<> (not equal to sign)	Not equal to	A1<>B1

Because the equal sign (=) is used to indicate a formula when you type text or a value in a cell, Excel evaluates what you type; however, this may cause unexpected filter results. To indicate an equality comparison operator for either text or a value, type the criteria as a string expression in the appropriate cell in the criteria range:

=''= entry ''

Where entry is the text or value you want to find. For example:

What you type in the cell

What Excel evaluates and displays

="=Davolio"

=Davolio

="=3000"

=3000

When filtering text data, Excel doesn't distinguish between uppercase and lowercase characters. However, you can use a formula to perform a case-sensitive search. For an example, see the section Wildcard criteria.

You can name a range Criteria, and the reference for the range will appear automatically in the Criteria range box. You can also define the name Database for the list range to be filtered and define the name Extract for the area where you want to paste the rows, and these ranges will appear automatically in the List range and Copy to boxes, respectively.

You can use a calculated value that is the result of a formula as your criterion. Remember the following important points:

The formula must evaluate to TRUE or FALSE.
Because you are using a formula, enter the formula as you normally would, and do not type the expression in the following way:

=''= entry ''
Do not use a column label for criteria labels; either keep the criteria labels blank or use a label that is not a column label in the list range (in the examples that follow, Calculated Average and Exact Match).

If you use a column label in the formula instead of a relative cell reference or a range name, Excel displays an error value such as #NAME? or #VALUE! in the cell that contains the criterion. You can ignore this error because it does not affect how the list range is filtered.
The formula that you use for criteria must use a relative reference to refer to the corresponding cell in the first row of data.
All other references in the formula must be absolute references.

Boolean logic: (Salesperson = "Davolio" OR Salesperson = "Buchanan")

Insert at least three blank rows above the list range that can be used as a criteria range. The criteria range must have column labels. Make sure that there is at least one blank row between the criteria values and the list range.
To find rows that meet multiple criteria for one column, type the criteria directly below each other in separate rows of the criteria range. Using the example, enter:

Type

Salesperson

Sales

="=Davolio"

="=Buchanan"
Click a cell in the list range. Using the example, click any cell in the range A6:C10.
On the Data tab, in the Sort & Filter group, click Advanced.
Do one of the following:
- To filter the list range by hiding rows that don't match your criteria, click Filter the list, in-place.
- To filter the list range by copying rows that match your criteria to another area of the worksheet, click Copy to another location, click in the Copy to box, and then click the upper-left corner of the area where you want to paste the rows.
  
  Tip When you copy filtered rows to another location, you can specify which columns to include in the copy operation. Before filtering, copy the column labels for the columns that you want to the first row of the area where you plan to paste the filtered rows. When you filter, enter a reference to the copied column labels in the Copy to box. The copied rows will then include only the columns for which you copied the labels.
In the Criteria range box, enter the reference for the criteria range, including the criteria labels. Using the example, enter $A$1:$C$3.

To move the Advanced Filter dialog box out of the way temporarily while you select the criteria range, click Collapse Dialog
.
Using the example, the filtered result for the list range is:

Type

Salesperson

Sales

Meat

Davolio

$450

produce

Buchanan

$6,328

Produce

Davolio

$6,544

Boolean logic: (Type = "Produce" AND Sales > 1000)

Insert at least three blank rows above the list range that can be used as a criteria range. The criteria range must have column labels. Make sure that there is at least one blank row between the criteria values and the list range.
To find rows that meet multiple criteria in multiple columns, type all the criteria in the same row of the criteria range. Using the example, enter:

Type

Salesperson

Sales

="=Produce"

>1000
Click a cell in the list range. Using the example, click any cell in the range A6:C10.
On the Data tab, in the Sort & Filter group, click Advanced.
Do one of the following:
- To filter the list range by hiding rows that don't match your criteria, click Filter the list, in-place.
- To filter the list range by copying rows that match your criteria to another area of the worksheet, click Copy to another location, click in the Copy to box, and then click the upper-left corner of the area where you want to paste the rows.
  
  Tip When you copy filtered rows to another location, you can specify which columns to include in the copy operation. Before filtering, copy the column labels for the columns that you want to the first row of the area where you plan to paste the filtered rows. When you filter, enter a reference to the copied column labels in the Copy to box. The copied rows will then include only the columns for which you copied the labels.
In the Criteria range box, enter the reference for the criteria range, including the criteria labels. Using the example, enter $A$1:$C$2.

To move the Advanced Filter dialog box out of the way temporarily while you select the criteria range, click Collapse Dialog
.
Using the example, the filtered result for the list range is:

Type

Salesperson

Sales

produce

Buchanan

$6,328

Produce

Davolio

$6,544

Boolean logic: (Type = "Produce" OR Salesperson = "Buchanan")

Insert at least three blank rows above the list range that can be used as a criteria range. The criteria range must have column labels. Make sure that there is at least one blank row between the criteria values and the list range.
To find rows that meet multiple criteria in multiple columns where any criteria can be true, type the criteria in the different columns and rows of the criteria range. Using the example, enter:

Type

Salesperson

Sales

="=Produce"

="=Buchanan"
Click a cell in the list range. Using the example, click any cell in the list range A6:C10.
On the Data tab, in the Sort & Filter group, click Advanced.
Do one of the following:
- To filter the list range by hiding rows that don't match your criteria, click Filter the list, in-place.
- To filter the list range by copying rows that match your criteria to another area of the worksheet, click Copy to another location, click in the Copy to box, and then click the upper-left corner of the area where you want to paste the rows.
Tip: When you copy filtered rows to another location, you can specify which columns to include in the copy operation. Before filtering, copy the column labels for the columns that you want to the first row of the area where you plan to paste the filtered rows. When you filter, enter a reference to the copied column labels in the Copy to box. The copied rows will then include only the columns for which you copied the labels.
In the Criteria range box, enter the reference for the criteria range, including the criteria labels. Using the example, enter $A$1:$B$3.

To move the Advanced Filter dialog box out of the way temporarily while you select the criteria range, click Collapse Dialog
.
Using the example, the filtered result for the list range is:

Type

Salesperson

Sales

produce

Buchanan

$6,328

Produce

Davolio

$6,544

Boolean logic: ( (Sales > 6000 AND Sales < 6500 ) OR (Sales < 500) )

Insert at least three blank rows above the list range that can be used as a criteria range. The criteria range must have column labels. Make sure that there is at least one blank row between the criteria values and the list range.
To find rows that meet multiple sets of criteria where each set includes criteria for one column, include multiple columns with the same column heading. Using the example, enter:

Type

Salesperson

Sales

Sales

>6000

<6500

<500
Click a cell in the list range. Using the example, click any cell in the list range A6:C10.
On the Data tab, in the Sort & Filter group, click Advanced.
Do one of the following:
- To filter the list range by hiding rows that don't match your criteria, click Filter the list, in-place.
- To filter the list range by copying rows that match your criteria to another area of the worksheet, click Copy to another location, click in the Copy to box, and then click the upper-left corner of the area where you want to paste the rows.
  
  Tip: When you copy filtered rows to another location, you can specify which columns to include in the copy operation. Before filtering, copy the column labels for the columns that you want to the first row of the area where you plan to paste the filtered rows. When you filter, enter a reference to the copied column labels in the Copy to box. The copied rows will then include only the columns for which you copied the labels.
In the Criteria range box, enter the reference for the criteria range, including the criteria labels. Using the example, enter $A$1:$D$3.

To move the Advanced Filter dialog box out of the way temporarily while you select the criteria range, click Collapse Dialog
.
Using the example, the filtered result for the list range is:

Type

Salesperson

Sales

Meat

Davolio

$450

produce

Buchanan

$6,328

Boolean logic: ( (Salesperson = "Davolio" AND Sales >3000) OR (Salesperson = "Buchanan" AND Sales > 1500) )

Insert at least three blank rows above the list range that can be used as a criteria range. The criteria range must have column labels. Make sure that there is at least one blank row between the criteria values and the list range.
To find rows that meet multiple sets of criteria, where each set includes criteria for multiple columns, type each set of criteria in separate columns and rows. Using the example, enter:

Type

Salesperson

Sales

="=Davolio"

>3000

="=Buchanan"

>1500
Click a cell in the list range. Using the example, click any cell in the list range A6:C10.
On the Data tab, in the Sort & Filter group, click Advanced.
Do one of the following:
- To filter the list range by hiding rows that don't match your criteria, click Filter the list, in-place.
- To filter the list range by copying rows that match your criteria to another area of the worksheet, click Copy to another location, click in the Copy to box, and then click the upper-left corner of the area where you want to paste the rows.
  
  Tip When you copy filtered rows to another location, you can specify which columns to include in the copy operation. Before filtering, copy the column labels for the columns that you want to the first row of the area where you plan to paste the filtered rows. When you filter, enter a reference to the copied column labels in the Copy to box. The copied rows will then include only the columns for which you copied the labels.
In the Criteria range box, enter the reference for the criteria range, including the criteria labels. Using the example, enter $A$1:$C$3.To move the Advanced Filter dialog box out of the way temporarily while you select the criteria range, click Collapse Dialog
.
Using the example, the filtered result for the list range would be:

Type

Salesperson

Sales

produce

Buchanan

$6,328

Produce

Davolio

$6,544

Boolean logic: Salesperson = a name with 'u' as the second letter

To find text values that share some characters but not others, do one or more of the following:

Type one or more characters without an equal sign (=) to find rows with a text value in a column that begin with those characters. For example, if you type the text Dav as a criterion, Excel finds "Davolio," "David," and "Davis."

Use a wildcard character.

Use	To find
? (question mark)	Any single character For example, sm?th finds "smith" and "smyth"
* (asterisk)	Any number of characters For example, *east finds "Northeast" and "Southeast"
~ (tilde) followed by ?, *, or ~	A question mark, asterisk, or tilde For example, fy91~? finds "fy91?"

Insert at least three blank rows above the list range that can be used as a criteria range. The criteria range must have column labels. Make sure that there is at least one blank row between the criteria values and the list range.
In the rows below the column labels, type the criteria that you want to match. Using the example, enter:

Type

Salesperson

Sales

="=Me*"

="=?u*"
Click a cell in the list range. Using the example, click any cell in the list range A6:C10.
On the Data tab, in the Sort & Filter group, click Advanced.
Do one of the following:
- To filter the list range by hiding rows that don't match your criteria, click Filter the list, in-place
- To filter the list range by copying rows that match your criteria to another area of the worksheet, click Copy to another location, click in the Copy to box, and then click the upper-left corner of the area where you want to paste the rows.
  
  Tip: When you copy filtered rows to another location, you can specify which columns to include in the copy operation. Before filtering, copy the column labels for the columns that you want to the first row of the area where you plan to paste the filtered rows. When you filter, enter a reference to the copied column labels in the Copy to box. The copied rows will then include only the columns for which you copied the labels.
In the Criteria range box, enter the reference for the criteria range, including the criteria labels. Using the example, enter $A$1:$B$3.

To move the Advanced Filter dialog box out of the way temporarily while you select the criteria range, click Collapse Dialog
.
Using the example, the filtered result for the list range is:

Type

Salesperson

Sales

Beverages

Suyama

$5,122

Meat

Davolio

$450

produce

Buchanan

$6,328