How to Return the Nth Match in a List in Excel

Return the 2nd, 3rd or nth order for a customer with INDEX and FILTER, the classic SMALL/IF method, and second-from-last.

You will learn to

• Return the nth match with INDEX and FILTER
• Return the nth match with INDEX, SMALL and IF
• Count matches from the end with CHOOSEROWS

How it works

Standard lookups stop at the first match. To get the nth, list every match and pick one: =INDEX(FILTER(D2:D12,B2:B12="Maple Deli"),2) returns the amount of Maple Deli’s second order. Change the 2 to a cell reference and the formula returns whichever occurrence you ask for.

In versions without FILTER, the classic approach builds a list of positions: =INDEX(A2:A12,SMALL(IF(B2:B12=G1,ROW(B2:B12)-ROW(B2)+1),G2)). IF keeps the positions of matching rows and SMALL picks the nth smallest. To count from the end, use CHOOSEROWS with a negative number, such as -2 for the second-most-recent match.

=INDEX(FILTER(D2:D12,B2:B12=G1),G2)