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.

**Table of Contents**hide

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

**FALSE**: To get an exact match of the lookup value.**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**.

`=VLOOKUP(C12&"*",B5:C10,2,FALSE)`

- 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)`

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

`=VLOOKUP("*"&C12&"*",B5:C10,2,FALSE)`

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

`=C5&D5`

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

`=VLOOKUP(C12&"*"&C13&"*",$B$5:$E$10,4,0)`

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