Wildcard lookups and include or exclude criteria

Author: Oscar Cronquist Article last updated on June 21, 2020

Multiple wildcard lookups and include or exclude criteria 1

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.

What's on this page

Wildcard lookups and include or exclude criteria [Array Formula]
Wildcard lookups and include or exclude criteria [Filter]
Get Excel *.xlsm file

Dave asks:

Hi Oscar,

This is great and compliments your Multiple Criteria and Multiple Matches series of posts very well. However, I have been trying to modify the array formulas in the 3rd and 4th posts of that series to cope with wildcard searches or part cell matches and thought this post may help but not so far, unfortunately.

I have tried numerous approaches but can't seem to get a multiple criteria series of "searches" (or counts) to work with anything but exact data.

I'm trying to search for part of a serial number e.g.(A110 within a string A110E12694369020 as one of my search inputs. In this case, it is always the first 4 digits but I'd like to be able to input just A1 or *A1* into a search cell and not rely on reviewing the first 4 characters as this is not always the result for these serial numbers.

I'm also looking for items with corresponding dates equal or before a certain due date search string - your 4th post to Lookup-with-multiple-criteria-and-display-multiple-search-results-using-excel-formula-part-4-john helped me resolve these criteria though.

Finally, I'm trying another 'wildcard' search and to find FSS (or *FSS*) OR the exact inverse of this - so NOT containing FSS (or *FSS*) - and both of these search criteria are proving elusive to me.

All the data I have is in a large table but I'm not sure if I can alter it to be an Excel Table (which might help), as it is in a sheet that is deleted and replaced by an irregular macro from another workbook and this may corrupt a Table setup(?). It can not be a Pivot as the data is in a shared workbook and Pivot Tables do not update when in shared documents - hence the Array solution I am trying to develop. I have successfully named the columns of ranges I need though without losing them on an update of data.

To summarise - I'd like to search by some form of a wildcard; *app* to return a positive result for "apple" and to search for the opposite; have criteria that would exclude "apple" using something like *app*

Although I have around 80 columns I'm just looking to return the serial number in say Column A based on these multiple criteria; Col B <= a date, Col C does NOT contain a wildcard string *whatever*, Col D DOES contain a different wildcard string, Col E EXACTLY equals a third search string etc. I am happy to build up the criteria for more columns beyond this if I can resolve the NOT and the wildcard elements.

I'd really appreciate some further pointers, thanks Oscar!

Array formula in cell B9:

=IFERROR(IF(COUNTA($B$5:$E$5)<>0, 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)), ""), "")

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

Copy above array formula
Select cell B9
Paste array formula
Press and hold Ctrl + Shift
Press Enter

How to copy array formula

Select cell B9
Copy cell B9 (Ctrl + c)
Paste to cell range C9:E9
Copy cell range B9:E9
Paste to cell range B10:E20

Explaining the formula in cell B9

Wildcard lookups and include or exclude criteria evaluate formula

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.

Go to tab "Formulas" on the ribbon.
Press with left mouse button on "Evaluate Formula" button on the ribbon. A dialog box appears, see image above.
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.

Wildcard lookups and include or exclude criteria data source

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.

Filter tool

Wildcard lookups and include or exclude criteria filter

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.

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

Wildcard lookups and include or exclude criteria filter tool contains list

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

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

Wildcard lookups and include or exclude criteria filter tool does not contain list

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

Partial match for multiple strings – AND logic
This article demonstrates formulas that let you perform partial matches based on multiple strings and return those strings if all […]

Perform multiple partial matches and return records – AND logic
Question: Can expand this equation set into more than two colums of data, say if I had a first, middle […]

Return multiple matches with wildcard vlookup
Mr.Excel had a "challenge of the month" June/July 2008 about Wildcard VLOOKUP: "The data in column A contains a series […]

Search each column for a string each and return multiple records – OR logic
RU asks: Can you please suggest if i want to find out the rows with fixed value in "First Name" […]

Partial match values and return value with highest level
This article demonstrates a formula that searches a cell (partial match) based on values in a table and returns a […]

Partial match based on two conditions
Question: I want to search two columns with two search strings? The strings can be anywhere in these two columns […]

Lookup value based on two critera – second criteria is the adjacent value and its position in a given list
This article demonstrates a formula that extracts items based on two conditions. The first condition (Location) is used to find […]

How to search for a string in a column
Question: How do i create a flexible search formula to search a list? Answer: The following formula let´s you search […]

Distribute values across numerical ranges
This article demonstrates how to distribute values into specific ranges with possible overlapping ranges. I have written articles about filter […]

Functions in this article

IFERROR IF COUNTA INDEX SMALL ISNUMBER SEARCH NOT MATCH ROW COLUMN

More than 1300 Excel formulas

Excel formula categories

Average Choose Combine Merge Compare Concatenate Conditional Formatting Count Count values Dates Duplicates Extract Filter Filter records Filtered values Filterxml Hyperlink If cell Index Match Logic Lookups Match Max Min Numbers in sum OR logic Overlapping Partial match Records Sequence Small Sort by Sort values Sum Sumifs Sumproduct String manipulation Time Unique distinct records Unique distinct values Unique records Unique values Vlookup Vlookup return values

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

3 Responses to “Wildcard lookups and include or exclude criteria”

Dave says:

October 23, 2013 at 2:42 am

Oh wow! Oscar you are amazing! That's absolutely brilliant and I can't wait to get into working through it and applying in my files to fully understand the details. That makes it so versatile now for me to utilise. I really don't know how to say thank you well enough. You're an absolute star and guru, thank you so much! There's beer (or whatever else you fancy) waiting for you in Oz one day! Such a fast response too, your website is an awesome resource Oscar, I've learnt so much!

Reply
Dave says:

October 23, 2013 at 1:21 pm

Hi Oscar, I've had great progress assigning this solution for my file but I do have one more question if I may....how woudl you modify one of the Include/Exclude arrays to search for a BLANK cell. In particular I'm looking at a column of dates and want to make those with NO DATE an included search criteria. Cheers!

Reply
- Oscar says:
  
  October 25, 2013 at 2:30 pm
  
  Dave,
  
  Sorry, I don´t know. The COUNTA function counts values in range B5:E5. A blank is not counted.

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.

Contact Oscar

You can contact me through this contact form