To get the nth match using INDEX and MATCH, you can use an array formula that uses the IF and SMALL functions to figure out the row number of the match.
In the example shown, the formula in I7 is:
Note that this is an array formula and must be entered with Control + Shift + Enter.
How this formula works
At the core, this formula is simply an INDEX formula that retrieves a value in an array at a given position. Nth is supplied as a value in column H, and all the “heavy” work that the formula does is to figure out the row from which to retrieve a value, where row corresponds to “nth” match.
The IF function does the work of figuring out which rows contain a match, and the SMALL function returns the nth value from that list.
So, inside of IF, the logical test is ids=id, which in our example yields this array:
{TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}
Note that the customer id matches at the 1st and 4th postions, which return TRUE.
To get a list of relative row numbers for the range we’re working with (ids), we use:
which produces this array:
{1;2;3;4;5;6;7}
which is used as the “value if true”. When IF is finished, this is the array result:
{1;FALSE;FALSE;4;FALSE;FALSE;FALSE}
Note we have valid row numbers for row 1 and row 2.
This array is then processed by SMALL, which is configured (using the value in H6) to return the 2nd smallest value which, in this case, is the number 4.
So, in the end, the formula reduces to
Which returns the value in row 4, from the named range “amts”, which is $125
