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.

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

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

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

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

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

- How to Vlookup Partial Match for First 5 Characters in Excel
- [Fixed!] Excel VLOOKUP Partial Match Not Working
- How to Use Excel VLOOKUP to Find the Closest Match
- Excel VLOOKUP for Partial Match in Table Array

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