Filter or Extract with a Partial Match

Assume you have a table consisting of a few cells having few values, and you want to filter out the set of records with the partial match. You might take it easy and would prefer to manually filter out the desired partial matching values into another table without any need for the formula; then congratulations because you are thinking right.

But let me add that it would be a big deal while dealing with a bulk of data in the table, and then doing this bulky task manually would be a foolish decision.

But there isn’t any need to worry about it because after carefully reading this article filtering out the set of records with the aid of partial matches will be a piece of cake for you.

filter with partial match1

So let’s get straight into it!

General Formula:

You can use the FILTER function in combination with the SEARCH function to choose data records based on a partial match. The formula in E4 is written as follows:

=FILTER(Data_range,ISNUMBER(SEARCH(B2,Filter_range))*(B2<>""),"Not Found")

Note: Data_range is name range for A2:C9, and Filter_range is antoehr name range for B2:B9.

filter with partial match1

Let’s See How This Formula Works

The motive is to extract a collection of records that match a partial text string in this example. We match one column in the data range A2:C9 or the “Region” column. The FILTER function (new in Excel 365) retrieves matched data from a range based on a logical filter, which is at the heart of this formula:

=FILTER(filter_data,filter_logic)

The task in this example is to build the logic required to match records based on a partial match. Because the FILTER function does not handle wildcards, we must use an alternative technique. In this situation, we use the SEARCH function in conjunction with the ISNUMBER function as follows:

=ISNUMBER(SEARCH(B2,Filter_range))

filter with partial match1

The SEARCH function seeks text input in cell E2 within the Filter_range name range. SEARCH returns the position of a result in the text if it finds one.

If SEARCH formula does not yield any results, it returns the #VALUE! error:

We have a match if SEARCH returns a number. Otherwise, we don’t have a match. We wrap the SEARCH function within the ISNUMBER function to transform this result into a simple TRUE/FALSE value. Only when SEARCH returns a number will ISNUMBER return TRUE.

We aren’t utilizing a wildcard like (“*”) to achieve a partial match, but the SEARCH + ISNUMBER combination acts similarly. SEARCH function will return a number if the search string is found anywhere in the text, and ISNUMBER will return TRUE if the search string is found anywhere in the text.

=FILTER(Data_range,ISNUMBER(SEARCH(B2,Filter_range)))

filter with partial match1

We now have a workable formula, but we still need to clean up a few things. First, if the FILTER function returns no results, it will produce a #CALC! error. We would add a text message for the “if_empty” argument to deliver a friendlier message:

=FILTER(Data_range,ISNUMBER(SEARCH(B2,Filter_range))*(B2<>""),"Not Found")

FILTER will now return “Not Found ” if the search text is not found.

Finally, we must deal with the circumstance where the search string in E2 is blank. Surprisingly, if the search text is an empty string, the SEARCH function will return the value 1.

If field B2 is empty, FILTER will return all results since ISNUMBER will joyfully return TRUE for number 1. To avoid this behavior, we add the following logic to the original logical expression:

=ISNUMBER(SEARCH(B2,Filter_range))*(B2<>"")

filter with partial match1

When B2 is not empty, the expression B2<>”” yields TRUE; otherwise, it returns FALSE. By the original SEARCH + ISNUMBER expression, when we would multiply the results of this expression, all TRUE results are “canceled out” when B2 is empty. This is a variation on Boolean logic.

 

Extract All Partial Match Using Index and Match function

Only Excel 365 supports the FILTER feature. It is feasible to put up a partial match formula in previous versions of Excel to produce more than one match, but it is more complicated. This following formula demonstrates one method based on INDEX and MATCH.

=INDEX($B$1:$B$5,AGGREGATE(15,6,(ROW($B$1:$B$5)-ROW($B$1)+1)/ISNUMBER(SEARCH($D$1,$B$1:$B$5)),E2))

Related Functions


  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel ROW function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
  • Excel SMALL function
    The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …
  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Excel ISNUMBER function
    The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
  • Excel SEARCH function
    The Excel SEARCH function returns the number of the starting location of a substring in a text string.The syntax of the SEARCH function is as below:= SEARCH  (find_text, within_text,[start_num])…
  • Excel AGGREGATE function
    The Excel AGGREGATE function returns an aggregate in a list or database and ignore errors or hidden rows. The syntax of the AGGREGATE function is as below:= AGGREGATE(function_num, options, ref1,[ref2])…
  • Excel Filter function
    The FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…
Related Posts

Extract all Partial Matches
extract all partial matches1

Just assume that you have a range of data that contains a text string and you want to extract the few partial matches into another separate range of cells; then you might think that it's not a big deal; because ...

If Cell Contains Certain Text OR Equals Certain Text

IF cell equals certain text IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to test values to see if they equal certain text like ...

How to Extract Text between Two Text Strings in Excel
extract text between two words1

This post will guide you how to extract text between two given text strings in Excel. How do I get text string between two words with a formula in Excel. Extract Text between Two Text Strings Assuming that you have ...

How to Return a Value If a Cell Contains a Specific Text in Excel
return value if cell contains certain value2

This post will guide you how to return a value if a cell contains a certain number or text string in Excel. How do I check if a Cell contains specific text and then return another specific text in another ...

Insert The File Path and Filename into Cell
insert filepath filename in cell5

This post will guide you how to insert the file path and filename into a cell in Excel. Or how to add a file path only in a specified cell with a formula in Excel. Or how to add the ...

Sort Cells by Specific word or words
sort cells by specific words5

This post will guide you how to sort cells or text values in a column based on a specific word even if the word is in the text string in the cell. How do I sort cells in a column ...

Highlight Rows
highlight rows9

This post will teach you how to highlight rows in a table with conditional formatting in Excel. You will learn that how to change the color of the entire rows if the value of cells in a specified column meets ...

How to replace all characters after the first specific character
replace after first commas3

This post will guide you how to replace all characters after the first match of a specific character with a new text string in excel. How to replace all substrings after the first occurrence of the comma character with another ...

How to extract text after the second or nth specific character (space or comma)
extract text after second comma2

In the previous post, we talked that how to extract text after the first occurrence of the comma character in excel. And this post explains that how to get a substring after the second or nth occurrence of the comma ...

How to extract text before the second or nth specific character (space or comma)
extract text before second comma4

Before we talked that how to extract text before the first space or comma character in excel. And this post will guide you how to extract a substring before the second or nth specific character, such as: space or comma ...

Sidebar