Excel Office

Excel How Tos, Tutorials, Tips & Tricks, Shortcuts

Get nth match with VLOOKUP in Excel

This tutorial shows how to Get nth match with VLOOKUP in Excel  using the example below;

Formula

=VLOOKUP(id_formula,table,4,0)
Explanation

To get the nth MATCH with VLOOKUP, you’ll need to add a helper column to your table that constructs a unique id that includes the count. If this isn’t practical, you can use an array formula based on INDEX and MATCH instead.

=VLOOKUP(id&"-"&I6,data,4,0)

How this formula works

This formula depends on a helper column, which is added as the first column to the source data table. The helper column contains a formula which builds a unique lookup value from an existing id and a counter. The counter counts the number of times the unique id has appeared in the data table.

Worked Example:   Create hyperlink with VLOOKUP in Excel

In the example, the formula cell B4 of the helper column looks like this:

=D4&"-"&COUNTIF($D$4:D4,D4)

This formula picks up the value in D4 and uses concatenation to add a hyphen, and the result of a COUNTIF function. The COUNTIF function uses an expanding range (the mixed reference $D$4:D4) to generate a running count of the id in the data.

Worked Example:   VLOOKUP without #N/A error in Excel

On the lookup side, VLOOKUP is used to fetch values form the table, taking into account the “nth” occurrence. The trick is to build a lookup value with the same structure as the values that appear in the helper column, taking into account “nth”. Again, this is done with concatenation, by adding a hyphen and a value that represents “nth” to a normal lookup value (pulled from the named range “id” in this case).

Worked Example:   One way to track attendance using Excel formula

Leave a Reply

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