Getting the 2nd matching value from a list using VLOOKUP formula

Situation

We know that VLOOKUP formula is useful to fetch the first matching item from a list. So what would you do if you need 2nd (or 3rd etc.) matching item from a list?

For eg. If you have below data, and you want to find out how much sales John made 2nd time, then VLOOKUP formula becomes quite useless. Or is it?!?

Data:

Solution

A simple solution to this problem would be sorting our data on sales person’s name. That way all Johns would line up one beneath another. And we just have to find the first John’s position and add 1 to it to get to 2nd occurrence. Like this =MATCH("John", C5:C17, 0) + 1

But sorting is not an option all the time. So there should be a better way to do this?

Well, there is. We just add a helper column before the sales person name and fill it with sales-person’s name & occurrence. (see the below data table).

For this we can use COUNTIF() Formula, like this: =C5&COUNTIF($C$5:C5,C5). Notice the $C$5:C5?, well the mix of absolute & relative references does the trick here and gets John1, John2… etc.

Now, to lookup 2nd occurance of John, all we do is, simply write =VLOOKUP("John2",...) and we are done.

Sample File

Download Example File – Getting the 2nd matching item from a list using VLOOKUP formula

The file includes few examples on how to fetch 2nd, 3rd etc. matches using lookup formulas. It also has some interesting (and challenging) home work for you. Download & play with it.

Similar Tips

• Excel SUMPRODUCT Formula – What is it, how to use it & examples
• Excel VLOOKUP Formula – What is it, how to use it & examples
• Excel SUMIF and COUNTIF Formulas – What are they, how to use & examples