How to Lookup Partial String Match in Excel

The VLOOKUP function is useful when you need to lookup a partial string match in Excel.

We can use the wildcard symbol ‘*’ to specify that we want partial matches when using the VLOOKUP function.

Wildcard characters allow you to perform advanced searching in Excel. By default, the VLOOKUP function searches for strings that are an exact match. Wildcard characters can be added to your search string to make your search function more powerful.

The most popular of these wildcard characters is the asterisk symbol. The asterisk symbol represents any number of characters. For example, the search string ‘Ex*’ can match with ‘Excel’, ‘Expert’, or ‘Extravagant’.

Let’s take a look at a quick example of a scenario where we can use the asterisk to perform a partial string match.

Suppose you have a table of employees where name data is stored in a single column. If you want to use VLOOKUP to get employee data, you will have to match the row with the employee’s full name.

We can use the asterisk character to find matches given just the employee’s first name or last name.

This use case is just one way to use the asterisk wildcard in Excel. This technique can also be used in conditional statements. For example, we can check if an employee position contains the word ‘Senior’ by using the conditional expression <cell>=”Senior*”. 

Users can also use the wildcard characters when performing find and replace operations.

Now that we know how versatile wildcard characters are, let’s look at a sample spreadsheet that uses the asterisk character for partial-string matches.

A Real Example of a Lookup for Partial String Matches in Excel

Let’s take a look at a real example of a spreadsheet that uses wildcard characters for partial string matching.

In the example below, we have a table of employee data that is listed according to the employee ID number. Users can search the table by the last name by typing their search term into cell H2. Even if the user returns a partial last name, the sheet will still try to match the search term with an employee.

In this particular example, a search for ‘Mac’ returns employee data for the employee ‘MacGee’.

To get the values in cells H5 onwards, we just need to use the following formula:

=VLOOKUP($H$2&"*",B:E,1,FALSE)

We use the concatenation symbol ‘&’ to add an asterisk after our search string in cell H2. This allows the VLOOKUP function to return a partial string match.

We can also use the asterisk when performing an INDEX and MATCH formula:

=INDEX(A:A,MATCH($H$2&"*",B:B,0))

You can make your own copy of the spreadsheet above using the link attached below. 

If you’re ready to create your own spreadsheet with partial string lookup, head over to the next section to read our step-by-step guide.

How to Lookup Partial String Match in Excel

This section will guide you through each step needed to start using partial-string matches in Excel. You’ll learn how we can use the wildcard character ‘*’ to allow VLOOKUP to perform partial matches in your spreadsheet.

1. First, let’s set up our lookup cell and the table to display our matching entry. In this sheet, the user will type the string to search for in cell H2. If a partial match is found, relevant data will be shown in the range H5:H9.
   
2. Next, select the cell with the first value to display. In this example, we’ll first work on returning the value for Last Name in cell H5.
   
3. Next, we can add our VLOOKUP formula with references to our lookup cell H2 and our main table.
   
4. We can drag down the formula in cell H5 to fill up the First Name, Job Title, and Shirt Size.
   
   
5. To get the ID number, we’ll have to use the INDEX and MATCH functions. VLOOKUP will not work for this value since the id is to the left of the last_name field.
   
   

Frequently Asked Questions (FAQ)

1. What wildcard characters are available in Excel?
   Excel has three different wildcard characters.
   The asterisk allows you to match with any number of characters in sequence. The question mark replaces one single character from a text. For example, the string ‘l?t’ can match with ‘lit’, ‘let’, and ‘lot’.
   The tilde character ‘~’ is used to nullify the effect of a particular character. For example, using the search term, ‘Ex~*’ allows you to search for the exact string ‘Ex*’.
2. Can I use partial-string matches to lookup numbers?
   Yes, you can perform partial-string matches on numbers, but you must first convert your original number to text. We can easily return a number into a given number format using the TEXT function.

That’s all you need to remember to start looking up partial strings in Excel. This step-by-step guide shows how easy it is to use the asterisk wildcard character to perform more advanced string searches.

Excel wildcard characters are just one of Excel’s many useful features that you may not have tried yet. With so many other Excel functions out there, you can surely find one that suits your workflow.

Are you interested in learning more about what Excel can do? Do subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

Subscribe