This article demonstrates three different ways to filter a data set if a value contains a specific string and if a cell is not equal (exclude) to a specific value. The image above shows an array formula in cell range B9:E20 that extracts values based on the conditions on row 5 and the drop-down lists on row 4.

1. Wildcard lookups and include or exclude criteria - Excel 365

Excel 365 dynamic array formula in cell B9:

Explaining formula in cell B9

=FILTER(Sheet2!A2:D21,(MMULT(IFERROR((B4:E4="Exclude")*SEARCH(B5:E5,Sheet2!A2:D21)*(B5:E5<>""),0),TRANSPOSE(COLUMN(B5:E5)^0))=0)*(MMULT((IFERROR((B4:E4="Include")*SEARCH(B5:E5,Sheet2!A2:D21)*(B5:E5<>""),0)>0)*1,TRANSPOSE(COLUMN(B5:E5)^0))>0))

Step 1 - Check if cells in row 4 equal "Include"

The equal sign lets you compare value to value, it also compares a value to multiple values returning an array of boolean values TRUE or FALSE.

B4:E4="Include"

becomes

{"Exclude", "Include", "Exclude", "Include"}="Exclude"

The curly brackets begins and ends an array of values.

{"Exclude", "Include", "Exclude", "Include"}="Exclude"

and returns

{TRUE, FALSE, TRUE, FALSE}

Step 2 - Check if cell range A2:D21 contains any of the values in B5:E5

The SEARCH function returns the number of the character at which a specific character or text string is found reading left to right (not case-sensitive)

Function syntax: SEARCH(find_text,within_text, [start_num])

SEARCH(B5:E5,Sheet2!A2:D21)

becomes

SEARCH({"","son","Mauritius",""},{"Jakob", "Fullerton", "Latvia", 12315; "Afia", "Dickerson", ... , 22985})

and returns

{1,#VALUE!,#VALUE!,1;1,7, ... ,1}

The SEARCH function returns a number corresponding to the position of the found string, an error is returned if not found.

Step 3 - Check if cells B5:E5are not empty

The less than and greater than signs lets you check if a value is not equal to another value. The result is a boolean value TRUE or FALSE.

B5:E5<>""

becomes

{"","son","Mauritius",""}<>""

and returns

{FALSE,TRUE,TRUE,FALSE}

Step 4 - Multiply arrays

The asterisk lets you multiply numbers and/or boolean values in an Excel formula, it works also with arrays.

This performs AND logic between two boolean values.

TRUE * TRUE = TRUE
TRUE * FALSE = FALSE
FALSE * TRUE = FALSE
FALSE * FALSE = FALSE

TRUE and FALSE are converted to their numerical equivalents:

TRUE - 1
FALSE - 0 (zero)

(B4:E4="Include")*SEARCH(B5:E5,Sheet2!A2:D21)*(B5:E5<>"")

becomes

{TRUE, FALSE, TRUE, FALSE}* {1,#VALUE!,#VALUE!,1;1,7, ... ,1} * {FALSE, TRUE, TRUE, FALSE}

and returns

{0,#VALUE!,#VALUE!,0;0,0, ... ,0}

Step 5 - Remove errors and replace them with 0 (zero)

The IFERROR function if the value argument returns an error, the value_if_error argument is used. If the value argument does NOT return an error, the IFERROR function returns the value argument.

Function syntax: IFERROR(value, value_if_error)

IFERROR((B4:E4="Include")*SEARCH(B5:E5,Sheet2!A2:D21)*(B5:E5<>""),0)

becomes

IFERROR({0,#VALUE!,#VALUE!,0;0,0, ... ,0},0)

and returns

{0, 0, 0, 0; 0, 0, ... , 0}.

Step 6 - Check if valie is larger than 0 (zero)

IFERROR((B4:E4="Include")*SEARCH(B5:E5,Sheet2!A2:D21)*(B5:E5<>""),0)>0

becomes

{0, 0, 0, 0; 0, 0, ... , 0}>0

and returns

{FALSE, FALSE, FALSE, FALSE; FALSE, FALSE, ... , FALSE}.

Step 7 - Convert boolean values to numerical values

The MMULT function can't work with boolean values, however, their numerical equivalents are fine.

(IFERROR((B4:E4="Include")*SEARCH(B5:E5,Sheet2!A2:D21)*(B5:E5<>""),0)>0)*1

becomes

{FALSE, FALSE, FALSE, FALSE; FALSE, FALSE, ... , FALSE}*1

and returns

{0, 0, 0, 0; 0, 0, ... , 0}

Step 8 - Calculate column numbers

The COLUMN function returns the column number of the top-left cell of a cell reference.

Function syntax: COLUMN(reference)

COLUMN(B5:E5)

returns

{2,3,4,5}

Step 9 - Convert numbers to 1

COLUMN(B5:E5)^0

becomes

{2,3,4,5}^0

and returns

{1,1,1,1}

Step 10 - Rearrange values

The TRANSPOSE function converts a vertical range to a horizontal range, or vice versa.

Function syntax: TRANSPOSE(array)

TRANSPOSE(COLUMN(B5:E5)^0)

becomes

TRANSPOSE({1,1,1,1})

and returns

{1; 1; 1; 1}

Step 11 - Sum numbers in array row-wise

The MMULT function calculates the matrix product of two arrays, an array as the same number of rows as array1 and columns as array2.

Function syntax: MMULT(array1, array2)

MMULT((IFERROR((B4:E4="Include")*SEARCH(B5:E5,Sheet2!A2:D21)*(B5:E5<>""),0)>0)*1,TRANSPOSE(COLUMN(B5:E5)^0))

becomes

MMULT({0, 0, 0, 0; 0, 0, ... , 0}, {1; 1; 1; 1})

and returns

{0; 1; 0; 0; 0; 0; 1; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 1; 0; 0}

Step 12 - Check i cells equals "Exclude"

The equal sign lets you compare value to value, it also compares a value to multiple values returning an array of boolean values TRUE or FALSE.

B4:E4="Exclude"

becomes

{"Exclude", "Include", "Exclude", "Include"}="Exclude"

and returns

{TRUE, FALSE, TRUE, FALSE}.

Step 13 - Check if numbers in array are equal to 0 (zero)

The MMULT function calculates the matrix product of two arrays, an array as the same number of rows as array1 and columns as array2.

Function syntax: MMULT(array1, array2)

MMULT(IFERROR((B4:E4="Exclude")*SEARCH(B5:E5,Sheet2!A2:D21)*(B5:E5<>""),0),TRANSPOSE(COLUMN(B5:E5)^0))=0

becomes

MMULT({0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0},{1; 1; 1; 1})=0

becomes

{0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}=0

and returns

{TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}.

Step 14 - Multiply arrays

The asterisk lets you multiply numbers and/or boolean values in an Excel formula, it works also with arrays.

This performs AND logic between two boolean values.

TRUE * TRUE = TRUE
TRUE * FALSE = FALSE
FALSE * TRUE = FALSE
FALSE * FALSE = FALSE

TRUE and FALSE are converted to their numerical equivalents:

TRUE - 1
FALSE - 0 (zero)

(MMULT(IFERROR((B4:E4="Exclude")*SEARCH(B5:E5,Sheet2!A2:D21)*(B5:E5<>""),0),TRANSPOSE(COLUMN(B5:E5)^0))=0)*(MMULT((IFERROR((B4:E4="Include")*SEARCH(B5:E5,Sheet2!A2:D21)*(B5:E5<>""),0)>0)*1,TRANSPOSE(COLUMN(B5:E5)^0))>0)

becomes

({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE})*({FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE})

and returns

{0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 1; 0; 0}

Step 15 - Filter records based on the array

The FILTER function extracts values/rows based on a condition or criteria.

Function syntax: FILTER(array, include, [if_empty])

FILTER(Sheet2!A2:D21,(MMULT(IFERROR((B4:E4="Exclude")*SEARCH(B5:E5,Sheet2!A2:D21)*(B5:E5<>""),0),TRANSPOSE(COLUMN(B5:E5)^0))=0)*(MMULT((IFERROR((B4:E4="Include")*SEARCH(B5:E5,Sheet2!A2:D21)*(B5:E5<>""),0)>0)*1,TRANSPOSE(COLUMN(B5:E5)^0))>0))

becomes

FILTER(Sheet2!A2:D21,{0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 1; 0; 0})

and returns

{"Bronson", "Hutcherson", "Guinea-Bissau", 32251;"Shaquille", "Dickerson", "Honduras", 40494;"Fabian", "Dickerson", "Iran", 11102}

Step 16 - Shorten the formula

The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.

Function syntax: LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

FILTER(Sheet2!A2:D21,(MMULT(IFERROR((B4:E4="Exclude")*SEARCH(B5:E5,Sheet2!A2:D21)*(B5:E5<>""),0),TRANSPOSE(COLUMN(B5:E5)^0))=0)*(MMULT((IFERROR((B4:E4="Include")*SEARCH(B5:E5,Sheet2!A2:D21)*(B5:E5<>""),0)>0)*1,TRANSPOSE(COLUMN(B5:E5)^0))>0))

v - B5:E5

z - Sheet2!A2:D21

w - (v<>"")

y - TRANSPOSE(COLUMN(v)^0)

x - SEARCH(v, z)

LET(v, B5:E5, z, Sheet2!A2:D21, w, (v<>""), y, TRANSPOSE(COLUMN(v)^0), x, SEARCH(v, z), FILTER(z, (MMULT(IFERROR((B4:E4="Exclude")*x*w, 0), y)=0)*(MMULT((IFERROR((B4:E4="Include")*x*w, 0)>0)*1, y)>0)))

Back to top

2. Wildcard lookups and include or exclude criteria - array formula

Array formula in cell B9:

You don't need to enter this formula as an array formula if you are an Excel 365 subscriber.

How to enter an array formula

1. Copy above array formula
2. Select cell B9
3. Paste array formula
4. Press and hold Ctrl + Shift
5. Press Enter

How to copy array formula

1. Select cell B9
2. Copy cell B9 (Ctrl + c)
3. Paste to cell range C9:E9
4. Copy cell range B9:E9
5. Paste to cell range B10:E20

Explaining the formula in cell B9

I recommend the "Evaluate Formula" tool if you are curious about a formula you don't understand. It is also great for troubleshooting formulas that return errors.

1. Go to tab "Formulas" on the ribbon.
2. Press with left mouse button on "Evaluate Formula" button on the ribbon. A dialog box appears, see image above.
3. Press with left mouse button on "Evaluate" button to see the next calculation step.

The underlined expression shows what part of the formula that will be evaluated next. The italicized is the most recent result.

Keep press with left mouse button oning the "Evaluate button to go through all calculation steps, press with left mouse button on the "Close" button to dismiss the dialog box.

Step 1 - Search for a text string

The SEARCH function returns a number representing the position of character at which a specific text string is found reading left to right.

SEARCH(find_text,within_text, [start_num])

SEARCH($B$5, Sheet2!$A$2:$A$21)

becomes

SEARCH("", {"Jakob"; "Afia"; "Keisha"; "Oliver"; "Roma"; "Frewan"; "Bronson"; "Giovanna"; "Katoka"; "Cyra"; "Shaquille"; "Jakob"; "Ashanti"; "Hamza"; "Gretchen"; "Siena"; "Maggie"; "Fabian"; "Jaydon"; "Derrell"})

and returns

{1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1}

Step 2 - Check if it is a number

The ISNUMBER function checks if a value is a number, returns TRUE or FALSE.

ISNUMBER(value)

ISNUMBER(SEARCH($B$5, Sheet2!$A$2:$A$21))

becomes

ISNUMBER({1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1})

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}

Step 3 - Check if cell B4 is "Include" or Exclude"

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(logical_test, [value_if_true], [value_if_false])

The following part checks if cell B4 equals "Include".

IF($B$4="Include", ISNUMBER(SEARCH($B$5, Sheet2!$A$2:$A$21)), NOT(ISNUMBER(SEARCH($B$5, Sheet2!$A$2:$A$21))))

becomes

IF(FALSE, ISNUMBER(SEARCH($B$5, Sheet2!$A$2:$A$21)), NOT(ISNUMBER(SEARCH($B$5, Sheet2!$A$2:$A$21))))

becomes

IF(FALSE, {TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}, {FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE})

and returns

{FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}

Step 4 - Add all arrays

There are four similar parts in the formula described in step 1 to 3, what is different are the cell references. Each part is customized for a column, the list has four columns and the formulas has as many parts as there are columns.

The plus sign applies OR logic to the arrays, for example, 0 + 1 + 0 returns 1. If you multiply the arrays you apply AND logic. 0*1*0 equals 0.

((IF($B$4="Include", ISNUMBER(SEARCH($B$5, Sheet2!$A$2:$A$21)), NOT(ISNUMBER(SEARCH($B$5, Sheet2!$A$2:$A$21)))))*($B$5<>"")+(IF($C$4="Include", ISNUMBER(SEARCH($C$5, Sheet2!$B$2:$B$21)), NOT(ISNUMBER(SEARCH($C$5, Sheet2!$B$2:$B$21)))))*($C$5<>"")+(IF($D$4="Include", ISNUMBER(SEARCH($D$5, Sheet2!$C$2:$C$21)), NOT(ISNUMBER(SEARCH($D$5, Sheet2!$C$2:$C$21)))))*($D$5<>"")+(IF($E$4="Include", ISNUMBER(SEARCH($E$5, Sheet2!$D$2:$D$21)), NOT(ISNUMBER(SEARCH($E$5, Sheet2!$D$2:$D$21)))))*($E$5<>""))

becomes

({0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}+{0;1;0;0;0;0;1;0;0;0;1;0;0;0;0;0;0;1;0;0}+{1;0;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1}+{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0})

and returns

{1; 1; 1; 1; 1; 1; 2; 1; 1; 1; 2; 1; 1; 1; 1; 1; 1; 2; 1; 1}.

Step 5 - Check if the number of criteria matches the array, if it does return the corresponding row number

The COUNTA function counts the non-empty or blank cells in a cell reference.

COUNTA(value1, [value2], ...)

IF(COUNTA($B$5:$E$5)=((IF($B$4="Include", ISNUMBER(SEARCH($B$5, Sheet2!$A$2:$A$21)), NOT(ISNUMBER(SEARCH($B$5, Sheet2!$A$2:$A$21)))))*($B$5<>"")+(IF($C$4="Include", ISNUMBER(SEARCH($C$5, Sheet2!$B$2:$B$21)), NOT(ISNUMBER(SEARCH($C$5, Sheet2!$B$2:$B$21)))))*($C$5<>"")+(IF($D$4="Include", ISNUMBER(SEARCH($D$5, Sheet2!$C$2:$C$21)), NOT(ISNUMBER(SEARCH($D$5, Sheet2!$C$2:$C$21)))))*($D$5<>"")+(IF($E$4="Include", ISNUMBER(SEARCH($E$5, Sheet2!$D$2:$D$21)), NOT(ISNUMBER(SEARCH($E$5, Sheet2!$D$2:$D$21)))))*($E$5<>"")), MATCH(ROW(Sheet2!$D$2:$D$21), ROW(Sheet2!$D$2:$D$21)), "")

becomes

IF(COUNTA($B$5:$E$5)={1; 1; 1; 1; 1; 1; 2; 1; 1; 1; 2; 1; 1; 1; 1; 1; 1; 2; 1; 1}, MATCH(ROW(Sheet2!$D$2:$D$21), ROW(Sheet2!$D$2:$D$21)), "")

becomes

IF(2={1; 1; 1; 1; 1; 1; 2; 1; 1; 1; 2; 1; 1; 1; 1; 1; 1; 2; 1; 1}, MATCH(ROW(Sheet2!$D$2:$D$21), ROW(Sheet2!$D$2:$D$21)), "")

becomes

IF(2={1; 1; 1; 1; 1; 1; 2; 1; 1; 1; 2; 1; 1; 1; 1; 1; 1; 2; 1; 1}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20}, "")

and returns

{""; ""; ""; ""; ""; ""; 7; ""; ""; ""; 11; ""; ""; ""; ""; ""; ""; 18; ""; ""}.

Now we know which records that match the conditions.

Step 6 - Find k-th smallest row number

The SMALL function returns the k-th smallest number in the array, it will also ignore text and blanks.

SMALL(IF(COUNTA($B$5:$E$5)=((IF($B$4="Include", ISNUMBER(SEARCH($B$5, Sheet2!$A$2:$A$21)), NOT(ISNUMBER(SEARCH($B$5, Sheet2!$A$2:$A$21)))))*($B$5<>"")+(IF($C$4="Include", ISNUMBER(SEARCH($C$5, Sheet2!$B$2:$B$21)), NOT(ISNUMBER(SEARCH($C$5, Sheet2!$B$2:$B$21)))))*($C$5<>"")+(IF($D$4="Include", ISNUMBER(SEARCH($D$5, Sheet2!$C$2:$C$21)), NOT(ISNUMBER(SEARCH($D$5, Sheet2!$C$2:$C$21)))))*($D$5<>"")+(IF($E$4="Include", ISNUMBER(SEARCH($E$5, Sheet2!$D$2:$D$21)), NOT(ISNUMBER(SEARCH($E$5, Sheet2!$D$2:$D$21)))))*($E$5<>"")), MATCH(ROW(Sheet2!$D$2:$D$21), ROW(Sheet2!$D$2:$D$21)), ""), ROW(A1))

becomes

SMALL({""; ""; ""; ""; ""; ""; 7; ""; ""; ""; 11; ""; ""; ""; ""; ""; ""; 18; ""; ""}, ROW(A1))

The ROW function returns the row number from the cell reference A1. A1 is a relative cell reference meaning it will change automatically when you copy the cell and paste it to adjacent cells.

This will ensure that only one value is returned per cell.

SMALL({""; ""; ""; ""; ""; ""; 7; ""; ""; ""; 11; ""; ""; ""; ""; ""; ""; 18; ""; ""}, ROW(A1))

becomes

SMALL({""; ""; ""; ""; ""; ""; 7; ""; ""; ""; 11; ""; ""; ""; ""; ""; ""; 18; ""; ""}, 1)

and returns 7.

Step 7 - Return value from table

The INDEX function returns a value from a cell range based on a row and column number.

INDEX(Sheet2!$A$2:$D$21, SMALL(IF(COUNTA($B$5:$E$5)=((IF($B$4="Include", ISNUMBER(SEARCH($B$5, Sheet2!$A$2:$A$21)), NOT(ISNUMBER(SEARCH($B$5, Sheet2!$A$2:$A$21)))))*($B$5<>"")+(IF($C$4="Include", ISNUMBER(SEARCH($C$5, Sheet2!$B$2:$B$21)), NOT(ISNUMBER(SEARCH($C$5, Sheet2!$B$2:$B$21)))))*($C$5<>"")+(IF($D$4="Include", ISNUMBER(SEARCH($D$5, Sheet2!$C$2:$C$21)), NOT(ISNUMBER(SEARCH($D$5, Sheet2!$C$2:$C$21)))))*($D$5<>"")+(IF($E$4="Include", ISNUMBER(SEARCH($E$5, Sheet2!$D$2:$D$21)), NOT(ISNUMBER(SEARCH($E$5, Sheet2!$D$2:$D$21)))))*($E$5<>"")), MATCH(ROW(Sheet2!$D$2:$D$21), ROW(Sheet2!$D$2:$D$21)), ""), ROW(A1)), COLUMN(A1))

becomes

INDEX(Sheet2!$A$2:$D$21, 7, COLUMN(A1))

The COLUMN function returns the column number from the cell reference A1. A1 is a relative cell reference meaning it will change automatically when you copy the cell and paste it to adjacent cells.

becomes

INDEX(Sheet2!$A$2:$D$21, 7, 1)

and returns "Bronson" in cell B9.

The image above shows the data source on worksheet sheet2. Bronson is found on relative row 7 in cell range Sheet2!$A$2:$D$21.

3. Wildcard lookups and include or exclude criteria - autofilter

The Filter tool built-in to Excel is mostly used to filter a list based on conditions. The difference between the Filter tool and the formulas is that there are a few more extra steps to apply conditions.

The formulas are perhaps harder to enter and customize, however, once they are there they are extremely easy to use.

I will in this tutorial describe how to filter a list based on if a cell contains a specific text string and another column does not equal a specific value. Here are the steps to apply a filter using the Filter tool.

1. Press with left mouse button on any cell in your data with the left mouse button.
2. Go to tab "Data" on the ribbon.
3. Press with left mouse button on the "Filter" button. Black arrows appear next to each column header name.
4. Press with left mouse button on an arrow next to a column you want to filter.
   
5. Press with left mouse button on "Text Filters".
6. Press with left mouse button on "Contains...". A dialog box appears.
   
7. Type "son", see image above. This will filter the list if values in column name "Last name" contains "son".
8. Press with left mouse button on OK button.

The image above shows the list after applying the first condition. Now on to the next condition.

1. Press with left mouse button on the arrow next to the column name "Country".
   
2. Press with left mouse button on "Text Filters", see image above.
3. Press with left mouse button on "Does Not Contain...". A dialog box appears.
   
4. Type "Mauritius", see image above.
5. Press with left mouse button on OK button.

Get the Excel file

Lookup-and-include-or-exclude-criteria.xlsx

Search and return multiple values category

Partial match and return multiple adjacent values
This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]

Partial match with two conditions and return multiple results
This article demonstrates a formula that extracts a row or record from a data table using two strings as criteria. […]

Search and display all cells that contain multiple search strings
Jerome asks, in this blog post Search for multiple text strings in multiple cells in excel : If the list […]

Excel categories

Comments (3)