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 2nd 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 2nd value only. You can also use it for any value (3rd, 4th, etc.) as you need.

Let’s say we need to locate purchases for 2nd 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 2nd matching value with Vlookup

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *