## Wildcard lookups and include or exclude criteria

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

*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:

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

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.
- Click "Evaluate Formula" button on the ribbon. A dialog box appears, see image above.
- Click "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 clicking the "Evaluate button to go through all calculation steps, click 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.

### Filter tool

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.

- Click any cell in your data with the left mouse button.
- Go to tab "Data" on the ribbon.
- Click the "Filter" button. Black arrows appear next to each column header name.
- Click an arrow next to a column you want to filter.

- Click "Text Filters".
- Click "Contains...". A dialog box appears.

- Type "son", see image above. This will filter the list if values in column name "Last name" contains "son".
- Click OK button.

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

- Click the arrow next to the column name "Country".

- Click "Text Filters", see image above.
- Click "Does Not Contain...". A dialog box appears.

- Type "Mauritius", see image above.
- Click OK button.

Search for a text string 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 […]

Lookup with multiple criteria and return multiple search results

Question: How do I search a list containing First name column and a last name column? I want to search […]

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

Search for multiple text strings in column – AND logic

Question: How do I search a list for two text strings and return a list with where both strings occurs? […]

Search columns for a string 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" […]

Row is returned if both search strings are found on the same row

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

Minh Hung asks: Hello Mr Oscar I have the matter to create a megaformula to categorize my list. For short […]

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

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

**Contact Oscar**

You can contact me through this contact form

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!

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!

Dave,

Sorry, I don´t know. The COUNTA function counts values in range B5:E5. A blank is not counted.