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.
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.
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.
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.
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.
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.
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.
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.
If we write the earlier formula, where we have used the asterisk sign later of the value. It will return an erroneous result.
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
Here asterisk at the first so the string can start with any character. Execute the formula, you will find the appropriate address as result.
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.
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.
If we use the asterisk sign at the end, still we will find the #N/A error.
Let’s use the asterisk sign on either side of the cell reference.
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.
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.
This formula will work for any value that is at the very beginning.
If the value is at the end, that will also be matched using this formula.
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.
The formula will be the following one.
Within the MATCH function, we have compared the values and returned the position when it was found.
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.
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.
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.
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.
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.
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.
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.
- VLOOKUP and Return All Matches in Excel (7 Ways)
- VLOOKUP with Numbers in Excel (4 Examples)
- VLOOKUP with Multiple Matches in Excel
- How to Use VLOOKUP for Partial Match in Excel (4 Ways)
- How to VLOOKUP Partial Text in Excel (With Alternatives)
- 10 Best Practices with VLOOKUP in Excel
- VLOOKUP Partial Match Multiple Values (3 Approaches)