How to get the last 3 entries for a customer in Excel
Excel formula
=TAKE(FILTER(A2:C12,B2:B12=F1),-3)Filter the customer's rows and take the last three: =TAKE(FILTER(A2:C12,B2:B12=F1),-3).
- Result on the example
- a 3-row list that spills from E3 in
E3 - Works in
- Excel 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in E3. Change any input and the result updates, or edit the formula to experiment.
How it works
FILTER returns all five Contoso orders in date order. TAKE with a negative count takes rows from the end, so -3 keeps the last three. Google Sheets has no TAKE; there, a second condition keeps rows that have fewer than 3 later Contoso orders below them, which is the same three rows.
=TAKE(FILTER(A2:C12,B2:B12=F1),-3)Result{3 rows × 3 cols} 46128, "Contoso", 760, 46135, …
A2:C1211 rows × 3 cols: 4/2/2026, "Contoso", $420, 4/5/2026, …A range: 11 rows × 3 columns.
B2:B1211 rows × 1 col: "Contoso", "Fabrikam", "Contoso", "Northwind", …A range: 11 cells in one column.
F1"Contoso"The value in F1.
B2:B12=F1{11 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …The = operator tests whether the two sides are equal.
FILTER(A2:C12,B2:B12=F1){5 rows × 3 cols} 46114, "Contoso", 420, 46121, …FILTER: Rows of a range that meet every condition. #N/A when none match.- range
A2:C1211 rows × 3 cols: 4/2/2026, "Contoso", $420, 4/5/2026, … - condition1
B2:B12=F1{11 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …
- range
-3-3Makes the value negative.
TAKE(FILTER(A2:C12,B2:B12=F1),-3){3 rows × 3 cols} 46128, "Contoso", 760, 46135, …TAKE: First (or last, if negative) rows or columns.- array
FILTER(A2:C12,B2:B12=F1){5 rows × 3 cols} 46114, "Contoso", 420, 46121, … - rows
-3-3
- array
In Google Sheets
Google Sheets needs a different formula: =FILTER(A2:C12,B2:B12=F1,COUNTIFS(B2:B12,F1,ROW(B2:B12),">"&ROW(B2:B12))<3). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =TAKE(SORT(FILTER(A2:C12,B2:B12=F1),1,-1),3) | spills 3 × 3Different result | Sorts newest first before taking three, so it works even if the list isn't in date order. Newest first: April 30, 23, 16. |
| =TAKE(FILTER(A2:C12,B2:B12=F1),3) | spills 3 × 3Different result | A positive count takes the first rows instead. The first three orders: April 2, 9 and 16. |
Common mistakes
- TAKE needs Excel 2024 or Microsoft 365. In Excel 2021, use
INDEX(FILTER(…),SEQUENCE(3,1,ROWS(FILTER(…))-2),{1,2,3})or sort newest first. - With fewer than three orders, TAKE returns the ones that exist.
- "Last" means last in the list. If rows aren't added in date order, sort by date first.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Get the last 3 entries for a customer
E3, return the last three orders (all columns) for the customer in F1.Questions
How do I total the last 3 orders?
Take the amounts and add them: =SUM(TAKE(FILTER(C2:C12,B2:B12=F1),-3)).
How do I make the 3 a cell?
Use a negative cell reference: =TAKE(FILTER(A2:C12,B2:B12=F1),-H1).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.