How to Use VLOOKUP to Find Approximate Match for Text in Excel

Get FREE Advanced Excel Exercises with Solutions!

In this tutorial, I will discuss several ways to find a partial match in text using Excel VLOOKUP approximate match. We all know that the VLOOKUP function finds a particular value in the leftmost column of a data range and returns a desired value in the same row from a column you specify. Unlike partially matching number values, an approximate match of text values is a bit tricky. So, let’s explore some examples of VLOOKUP partial matching of text values.


Excel VLOOKUP to Find Approximate Match for Text: 4 Practical Examples

Before finding an approximate match in Excel, we should look at the syntax of the VLOOKUP function, which is:

VLOOKUP (lookup_value, table_array, column_index_num, [range_lookup])

In the above syntax, the fourth argument (range_lookup) indicates whether we are looking for the exact match or an approximate match. Basically, we have two options available to choose from.

  1. FALSE: To get an exact match of the lookup value.
  2. TRUE: To get an approximate match of the lookup value.

By seeing the above discussion, you may think if we put TRUE as the fourth argument, we will get an approximate match of a lookup value in a range of data. Yes, you can get the partial match using this way if the lookup column contains numbers. This is because, when you sort a lookup column (containing numbers) in ascending order, the approximate match will return the next largest value that is less than the lookup value. However, if the lookup column contains text values, an approximate match will not work. In that case, we will use alternative ways such as using a wildcard in the first argument of the function. For example, I will use the asterisk (*) symbol as a wildcard. Remember, we will use the VLOOKUP function in exact match mode while using the wildcard.

1. Apply Wildcard in VLOOKUP to Find Partial Match (Text Begins with)

For example, I have a dataset containing several sales representatives’ names and their regarding sales amounts achieved. Now, I will search for the sales representative’s name beginning with ‘Brad’ and thus return the corresponding sales amount.

Apply Wildcard in VLOOKUP to Find Partial Match (Text Begins with)

Steps:

  • Write the below formula in Cell C14.
=VLOOKUP(C12&"*",B5:C10,2,FALSE)

Apply Wildcard in VLOOKUP to Find Partial Match (Text Begins with)

  • Press Enter.
  • As a result, the above formula returns the sales amount ($10,000) earned by Brad Miller.

🔎 How Does the Formula Work?

C12&”*”

Here, Ampersand (&) joints the value of Cell C12 (Brad) with the wildcard (*). As a result, the lookup value becomes Brad*. So, the VLOOKUP formula looks for the text that begins with Brad*. Brad* means the formula will look for any of the names starting with Brad, with zero/more characters subsequently (such as Brad, Bradley, Braden).

VLOOKUP(C12&”*”,B5:C10,2,FALSE)

The above formula looks for Brad* in the range B5:C10 and returns the sales amount from column 2. FALSE in the fourth argument indicates that exact match mode is used here.

Note:

Be careful with the duplicates. That means there are two names in my dataset that begin with Brad (Brad Miller and Bradly Shaw). So, if multiple partial matches are found, the above formula will return results only for the first match. As stated above, we got the match result for Brad, not for Bradly.

Read More: Use VLOOKUP to Find Partial Text from a Single Cell


2. Find Approximate Match Where Cell Value Ends with Particular Text

Now, I will match the sales representative’s name that ends with a particular text portion ‘son’ and thus get the earned sales amount.

Steps:

  • Write the below formula in Cell C14.
=VLOOKUP("*"&C12,B5:C10,2,FALSE)

Find Approximate Match Where Cell Value Ends with Particular Text

  • The above formula looks for the sales representative name ends with the text ‘son’ and returns the corresponding sales amount ($7,500) after hitting Enter.

Here, “*”&C12, this part of the formula results *son. That means the above formula will look for the names ending with the text value *son (such as son, Johnson, Richardson). The rest of the formula works as mentioned in Method 1.


3. Two Wildcards in VLOOKUP to Get ‘Contains Type’ Partial Match in Text

In the previous two methods, I have shown how to find text that begins/ends with a certain text value. Now, I will look if any of the sales representative’s names contain the text ‘Me’ at any position and thus find out the sales amount.

Steps:

  • Write the below formula in Cell C14.
=VLOOKUP("*"&C12&"*",B5:C10,2,FALSE)

Two Wildcards in VLOOKUP to Get ‘Contains Type’ Partial Match in Text

  • As a consequence, the above formula will look for the sales representative name containing ‘Me’ anywhere in the name and thus show the earned sales amount ($11,000) after pressing Enter.

Here, “*”&C12&”*”, this part of the formula results in *Me*. It means the above formula will search in the lookup column if any of the names contain the text ‘*Me*’.

Read More: How to Perform VLOOKUP with Wildcard in Excel


4. Get Approximate Match Multiple Texts with Helper Column and VLOOKUP Function

This time I will partially match multiple texts. For example, I have the below dataset, containing Sales Rep, Selling Item, and Sales Amount.

Get Approximate Match Multiple Texts with Helper Column and VLOOKUP Function

Steps:

  • First, I will create a ‘helper column’ at the leftmost side of my dataset to concatenate the values of columns C and D by typing the below formula in Cell B5.
=C5&D5

Get Approximate Match Multiple Texts with Helper Column and VLOOKUP Function

  • Hit Enter. Use Autofill (+) to copy the formula to the rest of the cells and the helper column will display the concatenated value of Sales Rep and Selling Item.

Get Approximate Match Multiple Texts with Helper Column and VLOOKUP Function

  • Now I will look for the value of Cell C12 and C13 in the helper column. To do that, type the following formula in Cell C15. Similar to previous methods, I have used the wildcard (*) and VLOOKUP exact match (here, 0 means FALSE) this time too.
=VLOOKUP(C12&"*"&C13&"*",$B$5:$E$10,4,0)

Get Approximate Match Multiple Texts with Helper Column and VLOOKUP Function

  • Hit Enter. As a result, I got the sales amount matched with the given conditions.

Read More: Use VLOOKUP to Find Multiple Values with Partial Match in Excel


Alternatives of Vlookup to Get Approximate Match for Text

Other than using the VLOOKUP function, we can use some other options to match texts partially.

Microsoft has a free add-in used for Fuzzy Lookup. Fuzzy Lookup is similar to approximate lookup.


Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.


Conclusion

In the above article, I have tried to discuss several examples of VLOOKUP approximate match text in Excel elaborately. Hopefully, these examples and explanations will be enough to solve your problems. Please let me know if you have any queries.


Related Articles


<< Go Back to VLOOKUP Partial Match | Excel VLOOKUP Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Hosne Ara
Hosne Ara

Hosne Ara is a materials and metallurgical engineer who loves exploring Excel and VBA programming. To her, programming is like a time-saving superhero for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B. Sc. in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, she's shifted gears and now works as a content developer. In this role, she... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo