Lookup Partial Text Match in Excel (5 Methods)

In Microsoft Excel, there are several useful methods to lookup partial text matches and then extract data based on that specific match. In this article, you’ll learn all the suitable methods to lookup partial text matches in Excel with the incorporation of different lookup functions and formulas.

1. Using VLOOKUP Function to Lookup Partial Text Match in Excel

In the following picture, there is a table containing the marks in different subjects for some students in an exam. Now based on the partial match of a text from Column B, we’ll extract the marks in a subject for a student.

For example, we can look for the text “Tick” in the column of Name. Based on the partial match, we’ll find out the actual name of that student and then extract the marks in math of the corresponding student from the table.

In this example, we’ll use the VLOOKUP function as this function looks for a value in the leftmost column of a table and then returns the value in the same row from the specified column. The generic formula of this VLOOKUP function is:

=VLOOKUP(lookup_value, table_array, col_index_number, [range_lookup])

Since we’re going to pull out the marks in math of a student whose name contains the text “Tick”, the required formula in the output cell D17 will be:

Or,

After pressing Enter, you’ll see the marks in math for Tickner at once.

2. Combining Excel INDEX-MATCH Functions to Lookup Partial Text Match

Now we’ll use the combination of INDEX and MATCH functions. The INDEX function returns a value or reference of the cell at the intersection of the particular row and column in a given range and the MATCH function returns the relative position of an item in an array that matches a specified value in a specified order.

We’re going to find a similar output as found in the previous method. So, in this section, the required formula in the output cell D18 will be:

Or,

Now press Enter and the formula will return “91”- the marks Tickner has obtained in math.

In this formula, the two MATCH functions define the row and column numbers of the student’s name and the subject respectively. INDEX function then returns the value at the intersection of those defined row and column numbers from the array.

3. Applying Excel XLOOKUP to Lookup Partial Text Match with Wildcard Character

The XLOOKUP function searches a range or an array for a match and returns the corresponding item from a second range or array. The generic formula of this function is:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Now we’ll use this XLOOKUP function directly to extract the marks in math of the student whose name contains the text “Tick” inside.

In the output cell D18, the required formula will be:

After pressing Enter, you’ll find a similar output as found in the previous two examples.

In this function, we’ve used “2” as the [match_mode] argument that denotes the wildcard character match. If you don’t use this argument, the function will return #N/A error as by default, the function will look for the exact match instead of the wildcard character match.

4. Joining XLOOKUP, ISNUMBER, and SEARCH Functions to Lookup Partial Text Match

If you opt to avoid the use of wildcard characters in the lookup function, then you have to apply the combined formula of XLOOKUP, ISNUMBER, and SEARCH functions.

The ISNUMBER function checks if a cell value is a numeric value or not. The SEARCH function returns the number of the character at which a specific character or a text string is first found, reading left to right. The generic formula of these two functions are as follows:

=ISNUMBER(value)

And

=SEARCH(find_text, within_text, [search_num])

So, the required formula in the output cell D18 will be:

After pressing Enter, the resultant value will be shown right away.

🔎 How Does the Formula Work?

• The SEARCH function looks for the text ‘Tick’ in the range of cell B5:B14 and returns an array of:

{#VALUE!;#VALUE!;1;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

• The ISNUMBER function then searches for the numeric value in that array and returns another array of boolean values:

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

• The XLOOKUP function then looks for the specified boolean value- TRUE in the array found in the previous step and extracts the row number of that value in the array of B5:B14.
• Finally, based on the return array of F5:F14, the XLOOKUP function draws out the marks in math of the student whose name contains the text- ‘Tick’ inside.

5. Merging Excel FILTER, ISNUMBER, and SEARCH Functions to Lookup Partial Text Match

In the last method, we’ll use the combination of FILTER, ISNUMBER, and SEARCH functions. The FILTER function here filters a range of cells or an array based on the given conditions. The generic formula of this function is:

=FILTER(array, include, [if_empty])

Since we’re dealing with a similar dataset, the required formula with the FILTER function in the output cell D18 will be:

Now press Enter and you’ll get the resultant value instantly.

In this formula, the FILTER function filters the range of cells- F5:F14 based on the boolean value- TRUE only. The combination of ISNUMBER and SEARCH functions returns the array of boolean values- TRUE and FALSE and defines the second argument of the FILTER function.

Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.

Concluding Words

I hope all the methods mentioned above will now help you to extract data based on the partial text matches in your Excel spreadsheets. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.

<< Go Back to Partial Match Excel | Formula List | Learn Excel