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.

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


Similar Readings:


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

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 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, here I got the sales amount matched with the given conditions.

Read More: Excel Partial Match Two Columns (4 Simple Approaches)


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

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo