Look Up the 2nd, 3rd or Nth Match in Excel

Look Up the 2nd, 3rd or Nth Match in Excel featured illustration

📎 This article includes 1 downloadable practice file ↓

⏱ 2 min read

“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.

In this article
  1. Excel 365: FILTER then INDEX
  2. Any Excel: SMALL + IF + INDEX
  3. Helper-column method (fast on huge sheets)
  4. Nth largest within a group
  5. Where people go wrong
  6. Practice
  7. Continue learning

Excel 365: FILTER then INDEX

Customer in L1, N in L2:

Advertisement
=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:

Advertisement
=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

Issue Fix
#NUM! from SMALL or AGGREGATE N is bigger than the number of matches; wrap in IFERROR
Wrong row returned Forgot -ROW(D2)+1, which turns sheet rows into positions
“2nd order” isn’t the 2nd by date Data isn’t sorted by date; SORT inside the formula

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.

Advertisement

More: all formula combos · Excel function course.

📎 Practice files for this article

  • 📗
    Formula combinations practice workbook50 tasks: lookups, running totals, ranks, top-N, unique counts, FY, age, text extraction, list comparison, weighted averages, ageing, rolling totals, duplicates and working days — with automatic checks.
    ⬇ XLSX · 39 KB

Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.

✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free · AI can be wrong