Combination
The five largest manual entries with TAKE, SORT and FILTER
List the five largest manual (MEM) journal lines for journal entry testing: FILTER keeps the MEM lines, SORT puts the largest amount first and TAKE keeps the first five rows.
Functions used:TAKESORTFILTERExcel 2024 or later (This combination needs Excel 2024 or later)
Worked example
Top five manual entries for journal entry testing
Scenario
For the audit of Veldhuis Installatie BV you test journal entries. Manual entries (journal MEM) carry a higher risk of management override, so you want the five largest ones first.
The journal lines come from the XAF auditfile, with sales (VRK) and purchase (INK) lines mixed in.
The data
| Row | A | B | C | D | E | F | G | H | I | J | K |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Journal | Entry no. | Date | Description | Amount | ||||||
| 2 | MEM | 25-0801 | 31 Mar 2025 | Holiday pay accrual | €18,400.00€ 18.400,00 | MEM=TAKE(SORT(FILTER(A2:E9, A2:A9="MEM"), 5, -1), 5)=TAKE(SORT(FILTER(A2:E9; A2:A9="MEM"); 5; -1); 5) | 25-0833 | 30 Jun 2025 | Revenue correction | €96,500.00€ 96.500,00 | |
| 3 | INK | 25-0412 | 2 Apr 2025 | Rent invoice | €52,300.00€ 52.300,00 | MEM | 25-0871 | 31 Dec 2025 | Bonus accrual | €41,200.00€ 41.200,00 | |
| 4 | MEM | 25-0833 | 30 Jun 2025 | Revenue correction | €96,500.00€ 96.500,00 | MEM | 25-0852 | 28 Nov 2025 | Rent reclass | €27,800.00€ 27.800,00 | |
| 5 | MEM | 25-0845 | 15 Sep 2025 | Reclass payables | €7,250.00€ 7.250,00 | MEM | 25-0801 | 31 Mar 2025 | Holiday pay accrual | €18,400.00€ 18.400,00 | |
| 6 | VRK | 25-0460 | 10 Oct 2025 | Sales invoice | €148,000.00€ 148.000,00 | MEM | 25-0845 | 15 Sep 2025 | Reclass payables | €7,250.00€ 7.250,00 | |
| 7 | MEM | 25-0871 | 31 Dec 2025 | Bonus accrual | €41,200.00€ 41.200,00 | ||||||
| 8 | MEM | 25-0880 | 31 Dec 2025 | VAT correction | €3,150.00€ 3.150,00 | ||||||
| 9 | MEM | 25-0852 | 28 Nov 2025 | Rent reclass | €27,800.00€ 27.800,00 | ||||||
| 10 |
Swipe sideways to see all columns
Formula in F2
=TAKE(SORT(FILTER(A2:E9,A2:A9="MEM"), 5, -1), 5)
=TAKE(SORT(FILTER(A2:E9;A2:A9="MEM"); 5; -1); 5)
Result: MEM | 25-0833 | 30 Jun 2025 | Revenue correction | €96,500.00 / MEM | 25-0871 | 31 Dec 2025 | Bonus accrual | €41,200.00 / MEM | 25-0852 | 28 Nov 2025 | Rent reclass | €27,800.00 / MEM | 25-0801 | 31 Mar 2025 | Holiday pay accrual | €18,400.00 / MEM | 25-0845 | 15 Sep 2025 | Reclass payables | €7,250.00MEM | 25-0833 | 30 Jun 2025 | Revenue correction | € 96.500,00 / MEM | 25-0871 | 31 Dec 2025 | Bonus accrual | € 41.200,00 / MEM | 25-0852 | 28 Nov 2025 | Rent reclass | € 27.800,00 / MEM | 25-0801 | 31 Mar 2025 | Holiday pay accrual | € 18.400,00 / MEM | 25-0845 | 15 Sep 2025 | Reclass payables | € 7.250,00
How the pieces fit together
Watch out: Amounts stored as text land above all numbers in a descending sort, so the top five shows those text lines instead of the largest amounts.
The formula taken apart, from the inside out. Each part can be typed into its own cell to see what it returns.
=A2:A9="MEM"
=A2:A9="MEM"
ReferenceJournal column, 8 cellsPress Escape to close=Equal toPress Escape to closeTextLiteral valuePress Escape to closeCompares each journal code in
A2:A9withMEM. The result is a column of eight values: TRUE, FALSE, TRUE, TRUE, FALSE, TRUE, TRUE, TRUE. Six of the eight lines are manual.=FILTER(A2:E9,
A2:A9="MEM") =FILTER(A2:E9;
A2:A9="MEM") FILTERReturns every row of a range that meets a condition.Read more about FILTERPress Escape to closearrayThe rows you want back: one column or a whole table.8 rows by 5 columnsPress Escape to closeincludeOne TRUE/FALSE per row, as tall as array (as wide for columns): what to keep.Journal column, 8 cellsPress Escape to close=Equal toPress Escape to closeTextLiteral valuePress Escape to closeKeeps the rows where the test is TRUE, with all five columns: 6 rows by 5 columns. The amounts are now in the order of the sheet: 18,400, 96,500, 7,250, 41,200, 3,150 and 27,800.
=SORT(FILTER(A2:E9,
A2:A9="MEM"), 5, -1) =SORT(FILTER(A2:E9;
A2:A9="MEM"); 5; -1) FILTERReturns every row of a range that meets a condition.Used here as: arrayThe range or array to sort; the source itself stays as it is.Read more about FILTERPress Escape to closearrayThe rows you want back: one column or a whole table.8 rows by 5 columnsPress Escape to closeincludeOne TRUE/FALSE per row, as tall as array (as wide for columns): what to keep.Journal column, 8 cellsPress Escape to close=Equal toPress Escape to closeTextLiteral valuePress Escape to closesort_index (optional)Which column of array to sort on, counted from 1; default is the first.Press Escape to closesort_order (optional)1 sorts ascending (default), -1 sorts descending.-1: descendingPress Escape to closeSorts the 6 MEM rows on column 5 (the amount), largest first (-1 means descending). The amounts now run 96,500, 41,200, 27,800, 18,400, 7,250, 3,150.
=TAKE(SORT(FILTER(A2:E9,
A2:A9="MEM"), 5, -1), 5) =TAKE(SORT(FILTER(A2:E9;
A2:A9="MEM"); 5; -1); 5) SORT SORTERENReturns a range sorted by one of its columns.Used here as: arrayThe range or array to take rows or columns from.Read more about SORTPress Escape to closeFILTERReturns every row of a range that meets a condition.Used here as: arrayThe range or array to sort; the source itself stays as it is.Read more about FILTERPress Escape to closearrayThe rows you want back: one column or a whole table.8 rows by 5 columnsPress Escape to closeincludeOne TRUE/FALSE per row, as tall as array (as wide for columns): what to keep.Journal column, 8 cellsPress Escape to close=Equal toPress Escape to closeTextLiteral valuePress Escape to closesort_index (optional)Which column of array to sort on, counted from 1; default is the first.Press Escape to closesort_order (optional)1 sorts ascending (default), -1 sorts descending.-1: descendingPress Escape to closerowsHow many rows to keep; negative counts from the end.3 = the first 3 rows. -3 = the last 3 rows. Empty = all rows.Press Escape to closeTAKE keeps the first 5 rows of the sorted list and drops the sixth (3,150). The result is 5 rows by 5 columns, with entry
25-0833of 96,500 on top.
Step by step
- A2:A9="MEM" tests the eight journal codes: MEM, INK, MEM, MEM, VRK, MEM, MEM, MEM gives TRUE, FALSE, TRUE, TRUE, FALSE, TRUE, TRUE, TRUE.
- FILTER drops the INK line (52,30052.300) and the VRK line (148,000148.000), which leaves 8 - 2 = 6 rows with the amounts 18,40018.400, 96,50096.500, 7,2507.250, 41,20041.200, 3,1503.150 and 27,80027.800.
- SORT with 5 and -1 orders those 6 rows on the amount, largest first: 96,50096.500, 41,20041.200, 27,80027.800, 18,40018.400, 7,2507.250, 3,1503.150.
- TAKE with 5 keeps the first five rows and drops the last one, entry
25-0880of 3,1503.150. - The result spills from
F2as 5 rows by 5 columns. The top row is25-0833, the revenue correction of 96,50096.500, and the bottom row is25-0845of 7,2507.250.
Why it works
Each function takes a whole table and returns a whole table, so they stack without helper columns. FILTER decides which lines count, SORT decides the order, and TAKE decides how many you see.
The order matters: sorting before filtering would work too, but taking before sorting would cut the list at the wrong place.
Watch out for
- The sort column is a position inside the filtered table (5 is the amount column), not a sheet column letter. If you filter fewer columns, the number must change with them.
- If no line has journal MEM, FILTER returns #CALC! and the formula shows an error. Give FILTER a third argument with a short message text if the file might hold no manual entries.
- Check that the amount column holds real numbers before you trust the top five.
Variations
=LARGE(IF(A2:A9="MEM",E2:E9), SEQUENCE(5))
=LARGE(IF(A2:A9="MEM";E2:E9); SEQUENCE(5))
The approach with LARGE, which also works in Excel 2021 where TAKE is missing: it returns only the five amounts (96,50096.500, 41,20041.200, 27,80027.800, 18,40018.400, 7,2507.250), not the entry numbers or descriptions, so you still need a lookup to see which entries they are.
=TAKE(SORT(FILTER(A2:E9,A2:A9="MEM"), 5, 1), -5)
=TAKE(SORT(FILTER(A2:E9;A2:A9="MEM"); 5; 1); -5)
Sorts ascending and takes the last five rows with a negative number. The same five entries, but with the largest at the bottom.
Related combinations
Functions used
- TAKEKeeps only the first or last rows and columns of a range, spilled as a block; a negative count takes from the end.
- SORTReturns a sorted copy of a range, by any one of its columns, ascending or descending, as a spilled block that re-sorts itself when the data changes.
- FILTERReturns every row of a range that meets your condition, as a live list that spills into the cells below and updates when the data changes.
Functions used
- TAKEKeeps only the first or last rows and columns of a range, spilled as a block; a negative count takes from the end.
- SORTReturns a sorted copy of a range, by any one of its columns, ascending or descending, as a spilled block that re-sorts itself when the data changes.
- FILTERReturns every row of a range that meets your condition, as a live list that spills into the cells below and updates when the data changes.