# How to Use the VLOOKUP Function to Find an Approximate Match for a Text in Excel – 4 Examples

This is the syntax of the VLOOKUP function:

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

The fourth argument (range_lookup) indicates whether we are looking for the exact match or an approximate match:

• FALSE: To get an exact match.
• TRUE: To get an approximate match.

If the lookup column contains text values, the function will not return an approximate match. You need to use a wildcard in the first argument. Here, the asterisk (*).

### Example 1 – Applying a Wildcard in VLOOKUP to Find a Partial Match (Text Begins with)

The dataset showcases sales representatives’ names and their sales amounts. To search for the sales representative’s name beginning with ‘Brad’ and see the corresponding sales amount:

Steps:

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

• Press Enter.

This is the output.

Formula Breakdown

C12&”*”

The Ampersand (&) joins the value of C12 (Brad) with the wildcard (*). The lookup value becomes Brad*. The VLOOKUP formula looks for the text that begins with Brad*: names starting with Brad, with zero or more characters (eg. Brad, Bradley, Braden).

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

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

Note:

Be careful with duplicates. The dataset contains two names beginning with Brad: Brad Miller and Bradly Shaw. If multiple partial matches are found, the formula will return the first match only.

### Example 2 – Finding an Approximate Match when the Cell Value Ends with a specific Text

To search sales representative’s names that end with ‘son’ and see their sales amount:

Steps:

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

• It looks for the sales representative name ending with ‘son’ and returns the corresponding sales amount (\$7,500) after pressing Enter.

“*”&C12, returns *son. The rest of the formula works as mentioned in Example 1.

### Example 3 – Using Two Wildcards in the VLOOKUP Function to Get a ‘Contains Type’ Partial Match

To search for the sales representative’s names containing ‘Me’ at any position and find the sales amount:

Steps:

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

• The formula will look for the sales representative’s names containing ‘Me’ and return the sales amount (\$11,000) after pressing Enter.

“*”&C12&”*”, returns *Me*.

### Example 4 – Getting an Approximate Match in Multiple Texts with a Helper Column and the VLOOKUP Function

The dataset showcases Sales Rep, Selling Item, and Sales Amount.

Steps:

• Create a ‘helper column’ to concatenate the values of columns C and D by entering the formula in B5.
`=C5&D5`

• Drag down the Fill Handle to see the result in the rest of the cells.

To look for the value of C12 and C13 in the helper column:

• Enter the following formula in C15.
`=VLOOKUP(C12&"*"&C13&"*",\$B\$5:\$E\$10,4,0)`

• Press Enter.

This is the output.

## Alternatives to the Vlookup Function to Get an Approximate Match for a Text

Microsoft has a free add-in: Fuzzy Lookup.

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF