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.

**Table of Contents**Expand

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

`=VLOOKUP(D16,B5:G14,5,FALSE)`

**Or,**

`=VLOOKUP("*Tick*",B5:G14,5,FALSE)`

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:

`=INDEX(B5:G14,MATCH(D17,B5:B14,0),MATCH(D17,B4:G4,0))`

**Or,**

`=INDEX(B5:G14,MATCH("*Tick*",B5:B14,0),MATCH(D17,B4:G4,0))`

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:

`=XLOOKUP("*"&D16&"*",B5:B14,F5:F14,,2)`

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:

`=XLOOKUP(TRUE,ISNUMBER(SEARCH(D16,B5:B14)),F5:F14)`

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:

`=FILTER(F5:F14,ISNUMBER(SEARCH(D16,B5:B14)))`

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