How to Use Partial VLOOKUP in Excel(3 or More Ways)

There may arise a situation where you need to perform a partial matching within VLOOKUP. To assist you, today we are going to show you how to operate partial matching using VLOOKUP. For this session, we are using Excel 2019, feel free to use any version.

First things first, let’s get to know about the workbook which is the base of our examples.

Dataset - Partial VLOOKUP

Here we have a table that contains several random persons with their addresses. Using this dataset, we will operate the partial match in VLOOKUP.

Note that this is a basic table to keep things straightforward. In a real-life scenario, you may encounter a much larger and complex dataset.

Practice Workbook

You are welcome to download the practice workbook from the link below.

Partial Match within VLOOKUP

For a partial match, we need to use the asterisk sign (*) as a wildcard. Any number of characters can take place in place of an asterisk. You will understand more when we will show examples.

Read more: How to Perform VLOOKUP with Wildcard in Excel

1. Match Value at the Beginning

We all know that the VLOOKUP function searches for a value within a given array and produces the result. So, we can provide the value within it to find the match and return the answer.

Let, we are going to search for a name and when the match is found, our formula will return the corresponding address.

Criteria and result field - Partial VLOOKUP

Before starting the operation, feel free to go through the article regarding VLOOKUP.

In this section, we will see how to match a value that is at the beginning of a string.

Criteria value - Partial VLOOKUP

Here we have brought the lookup value, Paul, since our dataset is a small one you may see that Paul is at the start of a name (Paul Adams).

So, the lookup value is at the beginning of the string, we need to build the formula such that the lookup_value starts with this value but after that, any number of characters can be there.

We will use an asterisk sign in the lookup_value field along with the given criteria.

=VLOOKUP(F4&"*",$B$4:$C$10,2,0) 

Formula asterisk at first - Partial VLOOKUP

F4 contains the searching value, Paul. And the asterisk sign denotes that any number of characters can take place. The ampersand (&) sign joins these two. So, our lookup_value stands as such, the value can be anything that starts with Paul.

Here our lookup_array is B4:C10 and we have used 2 as column_num since we need the value from the 2nd column.

Execute the code, it will provide the address.

Formula result - Partial VLOOKUP

Here we have found the address for Paul Adams.

2. Match Value at the End of String

In the earlier section, we have seen how to match the value at the end of the string. For this example, we have chosen the value, Smith.

New criteria value for new example

If we write the earlier formula, where we have used the asterisk sign later of the value. It will return an erroneous result.

Erroneous result - Partial VLOOKUP

Here we have found the #N/A error. The earlier formula was for when a value is at the beginning. To match the value that may be at the end of the string, we need to modify the formula.

The formula should be in such a way that there can be any characters at the start and the provided value at the end. So, the formula will be

=VLOOKUP("*"&F4,$B$4:$C$10,2,0)  

Formula asterisk at first - Partial VLOOKUP

Here asterisk at the first so the string can start with any character. Execute the formula, you will find the appropriate address as result.

Formula asterisk at first result - Partial VLOOKUP

3. Match Irrespective of the Position

If you need to match the value that can be either at the beginning or in the middle or at end of the string, then this section will be a helpful one.

Updated criteria value

Here, for example, we have chosen the value Gabrial. Now, if we use the asterisk sign at the beginning, then there will be an error.

Error in result value - Partial VLOOKUP

If we use the asterisk sign at the end, still we will find the #N/A error.

Error in result - Partial VLOOKUP

Let’s use the asterisk sign on either side of the cell reference.

=VLOOKUP("*"&F4&"*",$B$4:$C$10,2,0)

Here we have used two asterisk signs on either side of F4. This means any characters can be there at the beginning as well as at the end.

Formula asterisk at end and beginning

The lookup_value will match any value that contains Gabrial.

Here is one of our values is Robert Gabrial Jr. our formula will find Gabrial within it and the formula will return the address of the person.

Formula result

This formula will work for any value that is at the very beginning.

Formula result 2

If the value is at the end, that will also be matched using this formula.

Formula result 3

An Alternative to Partial VLOOKUP

We can use the combination of INDEX and MATCH functions. This can be a useful alternative to the VLOOKUP.

MATCH locates the position of a lookup value and INDEX returns the value from the given location.

To know about the functions visit these articles: INDEX, MATCH.

The formula will be the following one.

=INDEX($C$4:$C$10,MATCH("*"&F4&"*",$B$4:$B$10,0))

Within the MATCH function, we have compared the values and returned the position when it was found.

INDEX - MATCH formula

Then the INDEX function returns the value according to the location number. Since we want the output from the Residence column, we have provided C4:C10 as the array reference within INDEX.

INDEX - MATCH formula result

Advanced Use of Partial VLOOKUP

You may find several advanced scenarios to use vlookup. Here we are mentioning an advanced operation, where we will find all the values corresponding to the matched value.

For example, we have set the criteria value, Paul, and will find the addresses people named Paul live.

Populated Data

You might have noticed that we have populated the dataset, we did it for the purpose of this section.

Our formula will be built using the functions: IFERROR, INDEX, SMALL, IF, SEARCH and ROW.

Visit the articles to know about this function: IFERROR, SMALL, IF, SEARCH, and ROW

Let’s see the formula

=IFERROR(INDEX($C$4:$C$13, SMALL (IF (IFERROR (SEARCH ($E$5,$B$4:$B$13)>0,FALSE), ROW($B$4:$B$13)-ROW($B$4)+1), ROW(1:1))),"")

Here the INDEX function returns the values from C4:C13.

Advanced operation formula

The big SMALL portion propels us from which row (which index) we would like to return a value. Let’s debug the portion, the SEARCH function looks for the provided value and checks error-free values using IFERROR and IF.

The ROW functions assist to go after the values iteratively and then the SMALL function returns the values one by one, depending on the order.

The outer IFERROR function is set in such a way that it will return empty for any error value.

Use CTRL+SHIFT+ENTER to execute the formula.

Advanced operation result

It gave the first address for a person having Paul in his name. Now, drag down the formula, you will find the rest of the addresses.

Advanced operation complete result

Conclusion

That’s all for today. We have listed several approaches to use VLOOKUP for the partial match. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know any other methods that we might have missed here.


Further Readings:

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo