Get nth match with VLOOKUP

To get the nth match in a set of data with the VLOOKUP function, you can use a helper column that contains a special calculated lookup value. In the worksheet shown, the formula in cell I7 looks like this: =VLOOKUP($I$4&"-"&$H7,data,4,0) Where data is an Excel Table in the range B5:F16. When I4 contains "A", the formula will return the first 5 matches as it is copied down column I, using the numbers in column H for n. A similar formula can be used to return the amount associated with the nth match in cell J7. See below for details. Note: The purpose of this example is to explain how to get the nth match in Excel 2019 and older with VLOOKUP. It is also possible to use a formula based on INDEX and MATCH. However, in the current version of Excel, the FILTER function is a much better way to get multiple matching records.

April 14, 2025 · 1 min · 130 words · Debbie Patterson

The Helper column is used to create a special lookup value, as explained below.

If the product in H4 is changed, the formula should calculate a new result.

All data exists in an Excel Table nameddata.

Excel formula: Get nth match with INDEX / MATCH

The lookup value is created byconcatenatingthe product in I4 to a running count of the product.

The values in column B show the results of this formula.

“A-1”, “A-2”, etc.).

Excel formula: Running count of occurrence in list

Thetable_arrayis theExcel Tablenamed data.

To get the Name, we use 4 forcolumn_index_numand to get the Amount, we use 5.

Finally, we have the last value, which is zero (0).

Excel formula: VLOOKUP two-way lookup

Using zero (0) tells VLOOKUP to find anexact matchfor the id.

If the exact id isn’t found, VLOOKUP will return the #N/A error.

For a more information about VLOOKUP, see:How to use the VLOOKUP function.

Excel formula: VLOOKUP calculate grades

Excel formula: Merge tables with VLOOKUP

Excel VLOOKUP function

Excel COUNTIF function