The Vlookup function in Excel is great for pulling in data from a larger table. However one drawback to it is that it looks in the larger table and pulls over the first match it finds. But what if there are multiple instances in this larger table and you want to pull in the second or third instance of it? Well there are fixes to do this. I will go over three ways to do this.
1. Use an IF statement and COUNT function to display when you have multiple instances – When the VLOOKUP function pull in data, it will pull in the first match it finds. If there is only one match, then there is no problem. One way to fix this is to display the words “Multiple Matches” in the results instead of pulling in the first match.
So let’s use this example:
We have a list of car sales by city and state.
On the left, we want to pull in ‘cars sold’ from the main table array. We would do this by using the VLOOKUP function. However if we want to pull in cars sold in NY, you could see that there are 3 instances of NY in the large table array.
If we did the function =VLOOKUP(A2,F:G,2,FALSE) it will pull in the first match, which is 85.
However, we might want to know the second or third match. For this example, we want to know if there are multiple matches, so we need to add an IF statement before it to do that.
Look at the formula bar and let’s go over this formula.
We start off with an IF statement. The criteria of the IF statement is COUNTIF(F2:F12,A2)>1. What this does is count all the instances of cell A2 in the range of F2:F12. So this example counts 3 instances of NY. Since this is greater than 1, it displays the TRUE value.
The TRUE value is above the first arrow which is “Multiple Matches”. If the instances were not greater than 1, then it would display the FALSE result, which is the actual VLOOKUP.
2. Inserting a column and numbering your results – A second way to deal with multiple matches in a VLOOKUP is to use an inserted column to help you by numbering all the results. Then you can search for whatever nth match you want.
So let’s say we have this spreadsheet. You need to add a column to the left of your main table array.
We Want to insert a formula in our added column to number them. It would be :
We are taking K2 which is the initial name and adding a number to it. We get that number by counting which instance this cell is.
Countif is checking $K$2:K2. So as we copy it down, only the second K2 will change, since it is not anchored. Same with the last part of this, which is the item we are looking up. So in our example, we are counting the term Joe in cells K2:K2. There is 1. So our new cell becomes Joe1.
So now we a list of names with number after it for its instance. So we can look for the second match, third match or more with a basic VLOOKUP function…
3. Using OFFSET MATCH to get the second match – You can use the OFFSET MATCH functions within the VLOOKUP to replace the lookup table that you are searching for.
Let’s say you have this simple example:
You want to find the grades for Joe and Jill and pull them from the table at the right. So right now we have the basic VLOOKUP formula in cell H2 to pull that in.
This pulls in 44, as it is the first match for the name Joe. However, there are multiple instances of the name Joe in the table on the right.
What if we wanted the 2nd instance?
We could use the OFFSET MATCH formula to change the lookup range.
So looking at our VLOOKUP formula, we are looking up G2, which is Joe. We are looking in J2:K9. However, if we want to find the second match in this table, we would want to change this range we are looking in. So instead of looking in J2:k9, we need to find our first match and then add 1 to the range, so we start looking past the first match. This will pull in the second match.
To do this, we want to use the following formula to replace J2:K9
OFFSET will create the range that we want
J2 – this is where this range starts
MATCH – This will tell us where the first match is. It is looking for G2 which is Joe. It looks in J2:J20. It returns a 1 since Joe is in the first cell in that range.
So our OFFSET formula is now =OFFSET(J2,1,0,30,2)
This means our range starts at J2. It moves down 1 spot and over 0 spots. So our start is now J3. Then our range goes down 30 (arbitrary number to cover our lookup table) and 2 columns wide. So our final range is J3:K33.
Now when we use this OFFSET MATCH in place of J2:K9, it will actually be J3:K33 when looking up Joe. So it will pull the second match, which is 88.
In closing, those are 3 different ways to deal with multiple matches for VLOOKUPs. I hope these are helpful to you!
**TO get a 30 page Ebook preview of our first product “Master Excel Functions”, go to http://www.excelbyjoe.com/excelfunctions