How to Use Excel Table Search Function for Partial String Match

A partial string match is a way of finding a text value that contains or begins with a specific substring. For example, if you want to find all the names that start with “Mar” or contain “son” in a table, you can use a partial string match.

There are different ways to perform a partial string match in Excel, depending on the function and the criteria you use. Some of the common methods are:

Using the FILTER function with the SEARCH function and the ISNUMBER function. This method allows you to filter a table based on a partial match in one or more columns. The SEARCH function returns the position of the substring in the text, or an error if not found. The ISNUMBER function returns TRUE if the value is a number, or FALSE otherwise. The FILTER function returns the matching rows from the table, or a custom message if no match is found.
Using the VLOOKUP function with a wildcard character. This method allows you to look up a value in the first column of a table and return a corresponding value from another column, based on a partial match. The wildcard character (*) matches any sequence of characters, so you can use it to create a “begins with” or a “contains” match. You need to set the range_lookup argument to FALSE or 0 to enable the exact match mode when using wildcards.
Using the INDEX and MATCH functions with the TEXT function and a wildcard character. This method allows you to look up a value in any column of a table and return a corresponding value from another column, based on a partial match. The TEXT function converts a numeric value to a text value, so you can use the wildcard character (*) to match any sequence of digits. You need to enter the formula as an array formula, by pressing Ctrl + Shift + Enter, to make it work.

Procedures

Here are the steps to use each of the methods mentioned above:

FILTER with partial match

Select a cell where you want to enter the formula.
Type =FILTER( followed by the range of the table you want to filter, such as A2:D10.
Type a comma (,) and then ISNUMBER(SEARCH( followed by the substring you want to match, such as “Mar”.
Type a comma (,) and then the range of the column you want to search, such as B2:B10.
Type a closing parenthesis ()) and then multiply (*) the result by another logical expression, such as (H4<>“”) to exclude blank cells.
Type a comma (,) and then a custom message enclosed in double quotes (“”) to display if no match is found, such as “No results”.
Type a closing parenthesis ()) and press Enter to complete the formula.

VLOOKUP with partial match

Select a cell where you want to enter the formula.
Type =VLOOKUP( followed by the value you want to look up, such as “Mar”.
Type an ampersand (&) and then a wildcard character (), such as “Mar“.
Type a comma (,) and then the range of the table you want to search, such as A2:D10.
Type a comma (,) and then the column index number of the value you want to return, such as 3 for the ID column.
Type a comma (,) and then FALSE or 0 to enable the exact match mode.
Type a closing parenthesis ()) and press Enter to complete the formula.

INDEX and MATCH with partial match

Select a cell where you want to enter the formula.
Type =INDEX( followed by the range of the column you want to return, such as C2:C10.
Type a comma (,) and then MATCH( followed by a wildcard character (), such as ““.
Type an ampersand (&) and then the value you want to look up, such as 123.
Type another ampersand (&) and then another wildcard character (*), such as “123”.
Type a comma (,) and then TEXT( followed by the range of the column you want to search, such as D2:D10.
Type a comma (,) and then a format code enclosed in double quotes (“”) to convert the numeric values to text, such as “0”.
Type two closing parentheses ())) and then a comma (,).
Type 0 to enable the exact match mode.
Type a closing parenthesis ()) and press Ctrl + Shift + Enter to complete the array formula.

Explanation

To illustrate the use of these methods, let’s consider the following scenario:

You have a table of employee data in the range A2:D10, where column A contains the first name, column B contains the last name, column C contains the ID, and column D contains the salary. You want to perform a partial string match in different columns and return the corresponding values from other columns.

Here are some examples of the formulas you can use and the results they will return:

To filter the table based on the last name that contains “son”, you can use the formula:

=FILTER(A2:D10,ISNUMBER(SEARCH(“son”,B2:B10))*(H4<>“”),“No results”)

This formula will return the rows 4, 6, and 9 from the table, where the last name contains “son”. If the cell H4 is blank, the formula will return “No results”.

To look up the first name that begins with “Mar” and return the ID, you can use the formula:

=VLOOKUP(“Mar*”,A2:D10,3,FALSE)

This formula will return the value 456, which is the ID of the first employee whose first name begins with “Mar”. If there is no match, the formula will return #N/A.

To look up the salary that contains 123 and return the first name, you can use the formula:

=INDEX(A2:A10,MATCH(““&123&””,TEXT(D2:D10,“0”),0))

This formula will return the value “Mary”, which is the first name of the employee whose salary contains 123. If there is no match, the formula will return #N/A.

Other approaches

Besides the methods mentioned above, there are other ways to perform a partial string match in Excel, such as:

Using the XLOOKUP function with a wildcard character. This function is similar to VLOOKUP, but more flexible and powerful. It allows you to look up a value in any column of a table and return a corresponding value from another column, based on a partial match. You can also specify the match mode, the search mode, and the default value if no match is found. However, this function is only available in Excel 365 and Excel 2019.
Using the COUNTIF, SUMIF, or AVERAGEIF functions with a wildcard character. These functions allow you to count, sum, or average the values in a range that meet a partial match criterion. You can use the wildcard character (*) to match any sequence of characters, or the question mark (?) to match any single character. For example, you can use the formula:

=COUNTIF(B2:B10,“son”)

to count the number of last names that contain “son” in the range B2:B10.