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

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.

Download Practice Workbook

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

4 Examples of Excel VLOOKUP to Find Approximate Match for Text

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.

Steps:

• Write the below formula in Cell C14.

• 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 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 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.

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.

• 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 ends with the text value *son (such as son, Johnson, Richardson). The rest of the formula works as mentioned in Method 1.

Similar Readings

• Use VLOOKUP to Find Partial Text from a Single Cell
• How to Use VLOOKUP for Partial Match in Excel (4 Suitable Ways)
• Use IF Function to Find Partial Match in Excel (6 Ways)

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.

• 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 *Me*. It means the above formula will search in the lookup column if any of the names contain the text ‘*Me*’.

Read More: Lookup Partial Text Match in Excel (5 Methods)

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.

Steps:

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

• 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.

• 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.

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

Read More: How to Find Partial Match in Two Columns in Excel (4 Methods)

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. For instance:

➥ Fuzzy Lookup Add-In for Excel

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

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

• Excel SUMIF with Partial Match (3 Ways)
• Use COUNTIF Function for Partial Match in Excel
• Excel VLOOKUP to Find the Closest Match (with 5 Examples)
• How to Use Partial VLOOKUP in Excel (5 Suitable Examples)
• Use INDEX and Match for Partial Match (2 Easy Ways)
• How to Perform Partial Match of String in Excel (8 Easy Methods)