Find the second matching order for a customer in Excel with INDEX, SMALL, and IF
MATCH finds the first order a customer placed and stops. When the question is the second order — the repeat purchase, the follow-up — the formula has to walk every match in turn, not just the first.
Written 12 November 2026
Result
| Second order for R. Alvarez | 1005 |
| Amount of that order | $42.25 |
| Third order for R. Alvarez | 1009 |
| All three Alvarez orders | $120.55 |
| Second order for T. Brooks | No second order |
Download the spreadsheet: second-matching-order.xlsx (5,587 bytes)
It contains the ten-order list, the three array formulas that find the Alvarez orders in turn, and the Brooks lookup that has no second order to find.
1What you need
- Excel, Google Sheets, or LibreOffice. The formula in this tutorial is an array formula. In current Microsoft 365 it calculates as typed; in older Excel it must be confirmed with Ctrl+Shift+Enter, and LibreOffice asks for the same key combination (Create an array formula in Excel — Microsoft Support).
- The file above, or a blank workbook if you want to type the formulas in yourself. The file already holds the formulas in array form.
2The orders
Ten orders, rows 3 to 12. R. Alvarez ordered three times; most other customers ordered once or twice:
| Order ID | Customer | Amount |
|---|---|---|
| 1001 | J. Chen | $24.00 |
| 1002 | R. Alvarez | $54.50 |
| 1003 | M. Novak | $79.99 |
| 1004 | J. Chen | $19.95 |
| 1005 | R. Alvarez | $42.25 |
| 1006 | S. Patel | $12.75 |
| 1007 | T. Brooks | $68.00 |
| 1008 | S. Patel | $9.49 |
| 1009 | R. Alvarez | $23.80 |
| 1010 | M. Novak | $119.00 |
An ordinary INDEX and MATCH finds order 1002 and stops — MATCH returns the first match, and there is no “second” setting on it. Getting the second and third matches takes a formula that works through the whole customer column at once.
3How the formula walks the matches
The customer to find sits in F2 — R. Alvarez. The formula for the second matching order is:
=INDEX($A$3:$A$12, SMALL(IF($B$3:$B$12=$F$2, ROW($B$3:$B$12)-ROW($B$3)+1), 2))Inside out: IF tests every cell in the customer column against F2 and, for each row that matches, returns that row's position inside the range — positions 2, 5, and 9 for Alvarez. SMALL takes the second smallest of those positions, 5, and INDEX returns the order ID at position 5: 1005. The ROW part converts sheet rows into positions: the range starts at row 3, so ROW($B$3:$B$12)-ROW($B$3)+1 numbers the rows 1 to 10, the numbering INDEX expects.
The last argument of SMALL is the only difference between the matches: 1 finds order 1002, 2 finds 1005, and 3 finds 1009. The file holds all three, in F4, F5, and F6.
4The amounts, with a plain formula
Once an order ID is found, its amount is an ordinary lookup — the ID is unique, so MATCH's first match is the only match:
G4 =INDEX($C$3:$C$12,MATCH(F4,$A$3:$A$12,0))The three Alvarez amounts are $54.50, $42.25, and $23.80, and the block totals them:
F7 =SUM(G4:G6)
F8 =COUNTIF(B3:B12,F2)Total $120.55, across 3 orders. The COUNTIF earns its place: it says how many matches exist before you ask SMALL for the fourth one (Statistical functions (reference) — Microsoft Support).
5When there is no second order
Ask SMALL for a second match that does not exist and it returns an error — there is no second-smallest position in a list of one. T. Brooks ordered once, so the file's second block wraps the formula in IFERROR:
=IFERROR(INDEX($A$3:$A$12, SMALL(IF($B$3:$B$12=$F$10, ROW($B$3:$B$12)-ROW($B$3)+1), 2)), "No second order")The cell reads “No second order”. Wrap the formula the same way any time the count from step 4 might come back smaller than the match being asked for.
Traps
Pressing Enter alone in older Excel. Before Microsoft 365, an array formula confirmed with plain Enter does not evaluate the whole column — the formula must be entered as a legacy array formula with Ctrl+Shift+Enter (Create an array formula in Excel — Microsoft Support). The symptom is a plausible order ID from the wrong row.
Typing the curly braces yourself. Excel adds { } around an array formula when Ctrl+Shift+Enter confirms it. Braces typed by hand turn the entry into text, and the cell just shows the formula instead of calculating it.
Ranges that cover different rows. The positions SMALL picks come from the customer range, and INDEX spends them on the order ID range. If the two ranges start or end on different rows, every position lands one row off, and the formula returns the order next to the right one.
Sources
- Create an array formula in Excel — Microsoft Support — array formulas, and confirming them with Ctrl+Shift+Enter in versions before Microsoft 365.
- Look up values with VLOOKUP, INDEX, or MATCH — Microsoft Support — the INDEX and MATCH pattern, and MATCH returning a position.
- Statistical functions (reference) — Microsoft Support — SMALL returns the k-th smallest value in a data set; COUNTIF counts the cells that meet a criterion.