“What was this customer’s second order?” “Show the third payment from this vendor.” VLOOKUP and XLOOKUP stop at the first hit; here’s how to go further.

Excel 365: FILTER then INDEX

Customer in L1, N in L2:

=INDEX(FILTER(I2:I201, D2:D201 = L1), L2)                              ' Nth amount
=IFERROR(INDEX(FILTER(A2:A201, D2:D201 = L1), L2), "No such order")
=TAKE(FILTER(A2:I201, D2:D201 = L1), L2)                               ' first N whole rows
=CHOOSEROWS(FILTER(A2:I201, D2:D201 = L1), -1)                         ' last match, whole row

Want the Nth by date rather than by row order? Sort first: INDEX(SORT(FILTER(…)), L2).

Any Excel: SMALL + IF + INDEX

=INDEX(I2:I201, SMALL(IF(D2:D201 = L1, ROW(D2:D201) - ROW(D2) + 1), L2))

IF lists the positions of matching rows, SMALL picks the Nth smallest position and INDEX returns that row. In Excel 2019 and older, confirm with Ctrl+Shift+Enter. AGGREGATE does the same without array entry:

=INDEX(I2:I201, AGGREGATE(15, 6, (ROW(D2:D201) - ROW(D2) + 1) / (D2:D201 = L1), L2))

Helper-column method (fast on huge sheets)

Add a key in a spare column K: =D2 & "|" & COUNTIF($D$2:D2, D2) gives “Sharma Traders|1”, “Sharma Traders|2” and so on. Then use an ordinary lookup:

=XLOOKUP(L1 & "|" & L2, K2:K201, I2:I201, "Not found")

The running COUNTIF numbers each occurrence, and this scales to 100k rows without heavy array formulas.

Nth largest within a group

=LARGE(FILTER(I2:I201, D2:D201 = L1), 2)          ' customer's 2nd biggest order
=AGGREGATE(14, 6, I2:I201 / (D2:D201 = L1), 2)    ' any Excel 2010+

Where people go wrong

Practice

Download the combo practice workbook below. Its Sales sheet of 200 orders (dates, regions, products, customers, amounts) is ready data to try every formula on this page, and the Practice sheet has 50 checked tasks on related combos.

More: all formula combos · Excel function course.