Use Vlookup To Get The Nth Match

March 24, 2021 - by Bill Jelen

Challenge: Your lookup table contains multiple occurrences of each key field. You would like to return the second, third, or fourth occurrence of the key.

Solution: VLOOKUP cannot solve this problem. OFFSET with MATCH could do it, provided that the lookup table is sorted by key. But if your table is not sorted, you need to turn to this user-defined function from Zack Barresse and Peter Moran:

You need to add this function to your workbook’s VBA project. It works like VLOOKUP, but instead of specifying FALSE as the fourth argument, you specify which value match you want to return.

In Figure 57, a regular VLOOKUP appears in column F, and VLOOKUPNTH appears in columns G:H.

Figure 57. Formulas in G:H grab the nth match from the lookup table.

Breaking It Down: table array is a range passed to the function. When the function uses table_array, the future references to CELLS (nRow, 1) .Value always look through the first column of the lookup table. Later, VLOOKUPNTH is assigned to be Cells (nRow, Col index number). If Col Index Number contains the number N, this refers to the nth column of the lookup table.

Additional Details: In my seminars, I frequently lament that VLOOKUP cannot grab a value that appears to the left of the key field. You could use a similar approach to Barresse and Moran’s user-defined function to build a VLOOKUP that will work to the left of the key field:

VLOOKNEW is similar to VLOOKUP, except that you can use a negative value for the column index number (Figure 58).

Figure 58. VLOOKNEW returns a value that appears to the left of the key field

Summary: User-defined functions can provide improvements on the VLOOKUP function.

Source: "Vlookup 2nd value" on the MrExcel Message Board.

The post was nominated by Matt Hohbein. Thanks to Zack Barresse and Peter Moran.

Title Photo: Djim Loic on Unsplash

This article is an excerpt from Excel Gurus Gone Wild.