This tutorial will show you how to use the Partial Match Lookup (PML) function in Excel to extract the partial text or a wildcard from an Excel table.

This blog article will teach you how to use VLOOKUP wildcard in Excel to search for any partial text within a range of cells.

VLOOKUP is one of the most powerful Excel functions that allows you to search through columns for information. It can often be used to find answers to questions that are interesting, but are not exactly what you need. For instance, if you have a list of book titles, and you want to know the author of any of those books, it’s easy to use VLOOKUP to find the book’s author. Here’s how:

If you’re reading this, I’m guessing you’re already familiar with the VLOOKUP function. This function is so sensitive that it can identify even the smallest gaps in a cell, which the human eye would miss.

What if you’re unsure of the precise value you’re looking for? Is this to say that VLOOKUP is unable to discover a partial text match? That is far from the case.

You can search more effectively than Google using the VLOOKUP wildcard. You may use VLOOKUP to search for any portion of a cell’s content using this tool.

You may, for example, look for any value that starts or ends with a certain letter or string. Similarly, you may look for any cell that has a certain word string. Nothing could possibly be more adaptable.

Take a look at the table below as an example. Assume you wish to check up the pay of an employee named Kevin Ashton. You may search for him by his first name alone (Kevin*) or by his last name (*Ashton) using the asterisk wildcard. You may also look for names that start with K (K*) or finish with N (n*).

How-To-Use-VLOOKUP-Wildcard-In-Excel-Partial-Text-Match

The question mark (?) wildcard may also be used to substitute a single letter in a search. Kevin Ashton, for example, may be found by searching for Kein Ashton. VLOOKUP cannot conduct a partial match search by default without these wildcard characters.

Let’s get the ideas clear before I start demonstrating with examples.

You may download the sample file below to follow along:

https://softwareaccountant.com/wp-content/uploads/2019/04/VLOOKUP-wildcard-example-file.xlsm.xlsx

In Excel, what is a wildcard?

The wildcard is a symbol that may be used to do sophisticated searches. Wildcards are divided into three categories. They may be used to represent any character or set of characters in a search query.

A search for re*ter, for example, will return any word that starts with the letters re and ends with the letters ter. It will be detected if the data you’re looking at contains a term like reporter, like in the example above. This simply indicates that the asterisk (*) is used to replace the missing middle letters por in the search phrase. A wildcard sign is an asterisk (*) between two letters.

Symbols for wildcards in Excel

The three wildcard symbols in Excel are listed below.

  • The wildcard with an asterisk (*) matches any sequence of characters. It’s a placeholder for a group of one or more characters that appear after, before, or in between lines of text. This means you may use this wildcard to find words that start with a certain letter or letters, words that finish with a specific letter or letters, and words that include a specific letter or letters in between. In a single search, you may also utilize the asterisk wildcard as many times as you like. A search for re*t*r, for example, is legitimate. It stands in for two sets of characters that are absent. It’ll look for a word that starts with the letters re, has the letter t, and finishes with the letter r. Because they all meet the search term’s criteria, terms like reporter, repudiator, and requester may be found using this search.
  • The wildcard (?) is a question mark: The question mark is used as a wildcard to represent any single letter. For example, if you look for f?st, you’ll come up with fast or fist but not foist or first. This wildcard, unlike the asterisk, can only represent a single character.
  • The tilde () wildcard: This wildcard is used to look for the two special characters mentioned above. When searching for one of these special characters, such as an asterisk or a question mark, you must put the tilde () before it to prevent it from acting as a wildcard. For example, the value? and value* searches for cell values containing the question mark and asterisk symbols, respectively. When you see some instances, things will become obvious.

Examples of VLOOKUP wildcards

I believe you now have a good understanding of how Excel’s wildcard characters operate. Let me round up this tutorial with some practical VLOOKUP examples that include wildcard characters into the formula.

Example 1: Look for words or characters that start with a certain letter.

In this example, you’ll see how to utilize the asterisk wildcard character in the VLOOKUP function to do a partial match search.

Let’s suppose you wish to use the VLOOKUP function to find an employee by his or her first name and return his or her salary using the following data.

1629677267_381_How-To-Use-VLOOKUP-Wildcard-In-Excel-Partial-Text-Match

In the example above, the formula attempted to locate an employee with the first name Chase in order to get his pay. The formula, however, returned an error. This is due to the fact that there is no employee named Chase. Despite the fact that Chase Riley is listed, it is not the same as your search phrase.

Excel won’t know what you’re up to until you tell it. So, if you just want to search for workers by their first names, you’ll have to indicate it in your formula. This is when the wildcard VLOOKUP comes into play.

You don’t simply type Chase into the formula as in the example above to get a name that starts with Chase. The asterisk (*) wildcard symbol should be appended to the name Chase. This implies that instead of Chase, the search phrase should be Chase*.

The formula for this kind of search should be as follows:

=VLOOKUP(“Chase*”,Employee Info,5,FALSE)

Take a look at this example:

1629677268_656_How-To-Use-VLOOKUP-Wildcard-In-Excel-Partial-Text-Match

To find a cell whose content starts with a certain word or letters, just add the asterisk (*) sign to the beginning of the word or letters.

You may also look for names or phrases that start with a certain character or characters. For example, if you want to search for a name that starts with the letter C, just add the asterisk sign – C*.

The formula should then be as follows:

=VLOOKUP(“C*”,Employee Info,5,FALSE)

Example 2: Look for words or characters that finish in a certain way.

Let’s suppose you wish to find an employee’s pay by searching just for his or her last name. This is essentially the same job as the last one. The answer is also almost identical.

You shouldn’t simply type Riley into the formula to search for a name that ends in Riley. Instead, use the VLOOKUP wildcard (*) to prefix Riley’s last name. Instead of Riley, the lookup value in such a calculation should be *Riley.

The formula for this kind of search should be as follows:

=VLOOKUP(“*Riley”,Employee Info,5,FALSE)

Take a look at this example:

1629677269_984_How-To-Use-VLOOKUP-Wildcard-In-Excel-Partial-Text-Match

The concept is the same as it was in the last example. To find a cell whose content ends with a certain word or letters, use the asterisk (*) wildcard sign to prefix the word or letters.

Example 3 – Find a cell that contains certain letters.

You can also use the VLOOKUP wildcard to look for cells that contain a certain letter or letters in between the text. Consider the first column in the following example:

1629677270_695_How-To-Use-VLOOKUP-Wildcard-In-Excel-Partial-Text-Match

You’ll note that the product code field has prefixes and suffixes that are all the same. The numerical values between the codes, on the other hand, vary per product.

Our aim is to locate a product based only on numerical values between the codes and get the unit pricing for that product. So, let’s say you want to search up a product with the number 005 in the middle of the code.

In this instance, the asterisk (*) wildcard sign should be used to enclose the lookup value (005). As a result, the search phrase should be *005* rather than 005.

The formula for this kind of search should be as follows:

=VLOOKUP(“*005*”,Employee Info,4,FALSE)

Take a look at this example:

1629677272_391_How-To-Use-VLOOKUP-Wildcard-In-Excel-Partial-Text-Match

To find a cell that has a certain set of characters inside the cell’s content, just use the asterisk (*) wildcard sign to surround the characters.

Use a cell reference to obtain just the numerical values and concatenate the wildcard symbol within the function to make it more interesting. You’ll only have to enter the product code into the reference field if you need to get a pricing for a different product in the future.

Take a look at this example:

1629677273_532_How-To-Use-VLOOKUP-Wildcard-In-Excel-Partial-Text-Match

Cell D2 is assigned as the lookup value in the above worksheet, with two concatenated VLOOKUP wildcards that replace the characters before and after the product code. This implies that if you alter the contents of cell D2, the formula’s output will change as well.

Take a look at this example:

How-To-Use-VLOOKUP-Wildcard-In-Excel-Partial-Text-Match

Example 4: In a search query, omitting a single character

The purpose of this example is to show how the question mark wildcard symbol interacts with the VLOOKUP function.

The asterisk (*) is the most often used and popular wildcard sign in real life. This is due to the fact that it is considerably more versatile and can do all of the functions that the question mark can. Understanding the asterisk wildcard will give the question mark a lot more versatility.

Regardless, let’s look at how the question mark is utilized as a wildcard sign in VLOOKUP.

The first thing to remember is that the question mark wildcard only stands for one character inside a cell’s content. For example, if you look for f?st, you’ll come up with fast or fist but not foist or first. This wildcard, unlike the asterisk wildcard, can only represent a single character in a lookup value.

Let’s take a look at a hypothetical situation:

=VLOOKUP(“SACL005?”,Employee Info,4,FALSE)

The lookup value for this formula is a product code, with the question mark wildcard as a suffix.

Take a look at this example:

1629677276_666_How-To-Use-VLOOKUP-Wildcard-In-Excel-Partial-Text-Match

By removing the final character or letter in the code, the lookup value in the above algorithm will search for a product code. As a result, rather of looking for a product code of SACL005L, the formula will look for a product code of SACL005, eliminating the final character, L.

You may use the question mark to substitute as many characters as you like inside the same formula. For example, if you wish to search for a product code without the first three letters, just substitute a question mark for them.

This is how such a formula might look:

=VLOOKUP(“???L005?”,Employee Info,4,FALSE)

Each of the first three characters in this formula has been replaced with a question mark. This implies that the amount of question mark wildcards you employ will be determined by the number of characters you wish to replace.

Take a look at this example:

1629677277_85_How-To-Use-VLOOKUP-Wildcard-In-Excel-Partial-Text-Match

Let me now demonstrate why the asterisk wildcard is popular and favored over the question mark.

The formula utilizes the question mark symbol three times in the above example to replace the first three letters. When using the asterisk sign, however, you only need to use the wildcard symbol once to replace as many characters as you like. As a result, most people prefer the asterisk wildcard since it is easy to use.

Example 5: When searching, avoid using wildcard symbols.

Assume your data contains a cell with a question mark as one of the cell’s real values. Let’s say you wish to look for a cell that ends with a question mark.

Let’s apply everything we’ve learnt in the previous instances to this job. If you wish to search for a cell whose content ends with a certain character or characters, use the asterisk (*) wildcard symbol to prefix that character(s).

Let’s examine what happens if we prefix the question mark (?) symbol in the example below.

Take a look at this example:

1629677278_563_How-To-Use-VLOOKUP-Wildcard-In-Excel-Partial-Text-Match

You observe that the formula did not provide the desired outcome. It skips over the cell that matches our search phrase and goes straight to the lookup column’s first cell. Excel treats the question mark as a wildcard character rather than a regular cell value.

As a result, if you’re looking for a character that’s a wildcard symbol, you’ll need to instruct Excel to treat it as a regular cell value rather than a wildcard symbol. To do this, put a tilde () before the wildcard. So, let’s give it a go and see what happens this time.

Take a look at this example:

1629677279_33_How-To-Use-VLOOKUP-Wildcard-In-Excel-Partial-Text-Match

We now have the right answer.

Excel no longer recognizes the question mark as a wildcard as a result of the tilde () wildcard, therefore it isn’t treated as one.

This indicates that the tilde () wildcard is used to override the impact of the other two wildcards, allowing Excel to recognize the cell values as usual.

VLOOKUP wildcard and spaces as a bonus example

Because of leading and trailing spaces in the data, your VLOOKUP algorithm may fail at times. These are the gaps that appear before and after the content of a cell, respectively. When the data is imported, this happens most of the time.

When creating formulae, you may fix this kind of VLOOKUP issue by using Excel wildcard.

Consider the following scenario:

1629677279_343_How-To-Use-VLOOKUP-Wildcard-In-Excel-Partial-Text-Match

Jordan’s name is on the list of workers without a doubt. The VLOOKUP formula, on the other hand, returned an error because it believes the name we’re looking for (Jordan) isn’t accessible. Why is Excel unable to see it while we can?

This is due to the fact that the data contains additional spaces. To notice these additional areas, you need more than just your eyes. However, once these gaps are removed, your formula will function perfectly.

Take a look at this example:

1629677281_312_How-To-Use-VLOOKUP-Wildcard-In-Excel-Partial-Text-Match

When dealing with modest amounts of data, this method works effectively. It will be a tedious method in the event of large data.

You can create a formula that will disregard those additional spaces and get the value you’re searching for without any issues if you utilize VLOOKUP wildcard.

What’s more, the concept you learned in example three may help you address this issue. The aim was to replace certain letters before and after a specific text with the asterisk wildcard. To put it another way, you’re looking for cells that have certain values.

As a result, let’s modify the above algorithm to search without the additional spaces.

The formula is as follows:

=VLOOKUP(“*Jordan*”,B5:F17,5,FALSE)

1629677282_628_How-To-Use-VLOOKUP-Wildcard-In-Excel-Partial-Text-Match

The lookup value is surrounded by two asterisk wildcards, as you can see in the function’s first parameter. These wildcards instruct Excel to disregard any additional spaces or characters that appear before or after Jordan’s name.

With this approach, you may get around the difficulties that come with finding these difficult-to-find leading and trailing gaps.

VLOOKUP is a very useful formula that allows you to select the portion of a string that you want to use. For instance, you can use VLOOKUP to determine if a string contains a certain partial string.. Read more about excel partial match two columns and let us know what you think.

Frequently Asked Questions

How do you do a partial match VLOOKUP in Excel?

If you are looking for a partial match, then you would use the VLOOKUP function. For example, if you were to look up the first name of a person in your company, you would enter their last name as the lookup value and then select lookup from the drop-down menu.

How do you use index match with partial text in Excel?

The formula in the example is =INDEX(B2:B9,MATCH(A2,B2:B9,0)) which returns the value of cell B3.

How do you match a partial string in Excel?

You can use the INDEX function to match a string in Excel. For example, if you want to find all of the rows with abc in them, you would type INDEX(abc,0) and then press enter.

Related Tags

This article broadly covered the following related topics:

  • vlookup partial match not working
  • excel vlookup partial match
  • vlookup partial match multiple values
  • vlookup partial match
  • excel partial match two columns