Get the 2nd, 3rd, or Nth match with INDEX and FILTER

Replace legacy SMALL(IF(...)) array formulas with FILTER + INDEX

Before dynamic arrays, retrieving the 2nd invoice for "Acme" required brittle Ctrl+Shift+Enter INDEX/SMALL/IF row-offset formulas.

FILTER selects matching invoice values in source order; INDEX chooses the requested positive integer position. The SUMPRODUCT guard uses the same literal comparison as FILTER, so wildcard characters in a customer name do not change the match count.

Bounds-checked Nth match formula

With PickClient = "Acme" and Nth = 2, COUNTIF finds 3 matches (>= 2) and INDEX returns 1850.

=IF(OR(D2<1, D2<>INT(D2), SUMPRODUCT(--(A2:A5=C2)) < D2), "No Nth match", INDEX(FILTER(B2:B5, A2:A5=C2), D2, 1))

Out-of-bounds Nth check

Asking for the 4th match of "Acme" (when only 3 exist) cleanly returns "No Nth match" instead of #REF!.

=SUMPRODUCT(--(A2:A5=C2))

Further reading

• Microsoft Support — FILTER function