Finding 2nd Matching Value with Vlookup

There can be times that you need to look up for a value but not necessarily want the first matching value. Vlookup function cannot help you for a task like this, because it returns the first matching value. So what can we do for finding 2^nd matching value with Vlookup? A fast solution would be to make all the values unique so that we can use Vlookup function effectively.

Note: This technique is not limited to 2^nd value only. You can also use it for any value (3^rd, 4^th, etc.) as you need.

Let’s say we need to locate purchases for 2^nd customer whose name is Dexter. With the current state of our data, it is not possible to fetch that value with a Vlookup Function. If you use following formula, it will return $ 9,993, purchases for the first Dexter.

=VLOOKUP(“Dexter”;B2:B301;3;0) We need to add a helper column to make unique key values. To make this, insert a new column after column C and type following formula into this new empty column (column D) and copy it downward. =B2&”-“&COUNTIF($B$1:B2;B2) Here is how the data should look like: Now you can easily fetch purchases for second Dexter with Vlookup by using helper column value as lookup value. Following formula will fetch purchases for second Dexter and will result $ 3288 :

=VLOOKUP(“Dexter-2”;D2:E301;2;0)

You can download example workbook and exercise if you like:Fnding 2^nd matching value with Vlookup