Practically every time I do a training session discussing Vlookup I get the same question. I expect it and at times can anticipate at what point someone will raise their hand to inquire. “How do you alter the Vlookup function to get the second or third match?” By definition the Vlookup function will only find the first match but there is a way to trick the function to going to the next available option. Here is one approach:
Above I have a table containing social security numbers, names and the places where a person has lived. I would like to use the Vlookup formula to get the second and third residences in the list. By design the Vlookup function always returns the first match.
1.Insert a Count function in cell A4 as follows:
2. Next add a concatenation formula around the concatenation formula
3. Copy the new formula down
Now there is a new social security number which indicates if it is the first, second, third or fourth occurrence.
4. Enter a modified SSN to be searched for in cell G4 based on the match you would like to find. For example the second match for SSN 263-18-1324 would be 263-18-1324-3
5. Next in your condensed table enter your vlookup function in cell I4. Use the Helper column for the lookup value.
This is one way to force the vlookup function to return second and third matches in a dataset.
If you found this post helpful, sign-up for our monthly enewsletter:
