LARGE
GROOTSTE in Dutch Excel
Returns the k-th largest number in a range, so 1 gives the largest, 2 the second largest, and so on.
Last updated: 13 October 2026
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Invoice no. | Amount | 3rd largest | |
| 2 | F25-1101 | €18,450.00€ 18.450,00 | €15,600.00€ 15.600,00=LARGE(B2:B7, 3)=LARGE(B2:B7; 3) | |
| 3 | F25-1102 | €7,320.00€ 7.320,00 | ||
| 4 | F25-1103 | €22,900.00€ 22.900,00 | ||
| 5 | F25-1104 | €15,600.00€ 15.600,00 | ||
| 6 | F25-1105 | €9,840.00€ 9.840,00 | ||
| 7 | F25-1106 | €12,075.00€ 12.075,00 |
Swipe sideways to see all columns
Result: €15,600.00€ 15.600,00
=LARGE(B2:B7,3)
=LARGE(B2:B7;3)
What LARGE does and when to use it
What it does: it returns the k-th largest number in a range, for example the third largest sales invoice.
When to use it: to pick key items for testing: the five largest invoices, or the threshold above which every item is tested. MAX gives only the largest; LARGE reaches the second, fifth or tenth. To see which invoices these are, TAKE(SORT(...)) returns whole rows.
How Excel evaluates it
Excel collects the numbers from array and ignores blanks, text and TRUE or FALSE. It counts them, and if k is below 1 or above that count, it stops with #NUM!. Otherwise it ranks the numbers from large to small and returns the one at position k.
Equal values each take their own position: with two invoices of 21500, positions 1 and 2 are both 21500, and the next smaller value only appears at position 3. If k is an array, Excel repeats this for every element and returns an array of results.
Watch out: k is a position, not a value: LARGE(range, 3) is the third largest number. A k below 1 or above the count of numbers gives #NUM!.
Mental model
A podium ranking with no names on it: you ask who stands in third place, and you get the score, not the athlete. Two athletes with the same score occupy two places.
LARGE(array,k)
LARGE(array;k)
| Argument | Type | Meaning |
|---|---|---|
array | Required | The range or array of numbers to rank. |
k | Required | The position from the top: 1 is the largest, 2 the next, and so on. |
Return value:A number: the value at position k when the numbers are ranked from large to small
Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).
Worked examples
Beginner
Third largest sales invoice
You have exported six open sales invoices of Veldhuis Installatie BV to a sheet. For the test of existence you will select the largest invoices, and you first want the amount of the third largest one to see where the top 3 starts. The amounts are in B2:B7.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Invoice no. | Amount | 3rd largest | ||
| 2 | F25-1101 | €18,450.00€ 18.450,00 | €15,600.00€ 15.600,00=LARGE(B2:B7, 3)=LARGE(B2:B7; 3) | ||
| 3 | F25-1102 | €7,320.00€ 7.320,00 | |||
| 4 | F25-1103 | €22,900.00€ 22.900,00 | |||
| 5 | F25-1104 | €15,600.00€ 15.600,00 | |||
| 6 | F25-1105 | €9,840.00€ 9.840,00 | |||
| 7 | F25-1106 | €12,075.00€ 12.075,00 | |||
| 8 |
Swipe sideways to see all columns
Formula in D2
=LARGE(B2:B7,3)
=LARGE(B2:B7;3)
Result: €15,600.00€ 15.600,00
Step by step
- Excel reads
B2:B7and finds six numbers: 18450, 7320, 22900, 15600, 9840 and 12075. The second argument, k, is 3. - k = 3 is between 1 and 6, so the position exists.
- Ranked from large to small the list is 22900, 18450, 15600, 12075, 9840, 7320.
- Position 1 is 22900 (
F25-1103), position 2 is 18450 (F25-1101), position 3 is 15600. - LARGE returns 15600, the amount of
F25-1104in cellB5. Your table itself stays in its original order. - Change the 3 to 1 and
D2becomes 22900, the same as MAX(B2:B7). Change it to 7 andD2becomes #NUM!, because there are only six numbers.
Watch out: k is a position, not an amount. LARGE(B2:B7, 15600) gives #NUM!, because the list does not have 15600 positions.
More on this example: why it works, 1 variation
Why it works
LARGE ranks the values by size and ignores where they sit in the sheet, so the order of the invoices does not matter. The result is an amount, not an invoice number; to learn which invoice it belongs to, look the amount up with XLOOKUP.
Variations
=SMALL(B2:B7,3)
=SMALL(B2:B7;3)
SMALL counts from the bottom instead. It returns 12075, the third smallest. Use it to find the lowest items, for example for a test of completeness near zero.
Intermediate
Top 5 invoices for item testing, with ties
For the revenue test at Veldhuis Installatie BV you select the five largest sales invoices as key items. The ranks 1 to 5 are typed in D2:D6, and the formula in E2 is copied down. Two pairs of invoices have the same amount, so you need to see how LARGE treats them.
| Row | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Invoice no. | Amount | Rank | Amount at rank | ||
| 2 | F25-2001 | €21,500.00€ 21.500,00 | 11 | €21,500.00€ 21.500,00=LARGE($B$2:$B$8, D2)=LARGE($B$2:$B$8; D2) | ||
| 3 | F25-2002 | €8,900.00€ 8.900,00 | 22 | |||
| 4 | F25-2003 | €21,500.00€ 21.500,00 | 33 | |||
| 5 | F25-2004 | €13,250.00€ 13.250,00 | 44 | |||
| 6 | F25-2005 | €17,800.00€ 17.800,00 | 55 | |||
| 7 | F25-2006 | €4,120.00€ 4.120,00 | ||||
| 8 | F25-2007 | €13,250.00€ 13.250,00 | ||||
| 9 |
Swipe sideways to see all columns
Formula in E2
=LARGE($B$2:$B$8,D2)
=LARGE($B$2:$B$8;D2)
Result: €21,500.00€ 21.500,00
Step by step
- In
E2, Excel readsD2, which holds 1, and the range$B$2:$B$8with seven amounts: 21500, 8900, 21500, 13250, 17800, 4120 and 13250. - Ranked from large to small: 21500, 21500, 17800, 13250, 13250, 8900, 4120. Equal amounts each keep their own position.
- Position 1 is 21500, so
E2shows 21500. - In
E3the referenceD2has becomeD3(k = 2), while$B$2:$B$8stays the same because of the $ signs. Position 2 is again 21500: the second invoice of that amount. E4(k = 3) gives 17800,E5(k = 4) gives 13250 andE6(k = 5) gives 13250 again, the second invoice of that amount.- A sixth position would give 8900. Because the range is fixed in the formula, add rows inside
$B$2:$B$8(insert a row above the last one) so the ranking includes them.
Watch out: Looking up the invoice number for each amount with XLOOKUP returns F25-2001 for both 21500 rows, because XLOOKUP stops at the first match. You then test one invoice twice and miss F25-2003.
More on this example: why it works, 1 more pitfall, 1 variation
Why it works
The $ signs lock the range so every copy ranks the same seven amounts, while the relative D2 moves to the next rank. Because LARGE gives equal amounts separate positions, the list always has as many entries as ranks you ask for, but it cannot tell you which of two equal invoices sits at which position.
More to watch out for
- Without the $ signs the range shifts down when you copy the formula:
E3would rankB3:B9and quietly skip the first invoice.
Variations
=TAKE(SORT(A2:B8,2, -1), 5)
=TAKE(SORT(A2:B8;2; -1); 5)
Returns the five largest rows with their invoice numbers, so ties stay visible as separate invoices. Prefer it when you are on Excel 2024 or Microsoft 365 and need to know which invoices to test.
Advanced
Second largest invoice of one customer, with a rank check
In the invoice list of Veldhuis Installatie BV you want the second largest invoice of one customer, entered in F1, with the position in F2. If the customer is not in the list, or the position is below 1 or above the number of invoices, the sheet must say so in words and not show #NUM!. The same formula will be reused for other customers and positions.
| Row | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Invoice no. | Customer | Amount | Customer | KL-014 | ||
| 2 | F25-3001 | KL-014 | €12,400.00€ 12.400,00 | Position (k) | 22 | ||
| 3 | F25-3002 | KL-027 | €30,800.00€ 30.800,00 | Amount | €18,900.00€ 18.900,00=IF(COUNTIF(B2:B9, F1) = 0, "Customer not found", IF(OR(F2 < 1, F2 > COUNTIF(B2:B9, F1)), "Position outside the customer's invoices", LARGE(IF(B2:B9 = F1, C2:C9), F2)))=IF(COUNTIF(B2:B9; F1) = 0; "Customer not found"; IF(OR(F2 < 1; F2 > COUNTIF(B2:B9; F1)); "Position outside the customer's invoices"; LARGE(IF(B2:B9 = F1; C2:C9); F2))) | ||
| 4 | F25-3003 | KL-014 | €9,650.00€ 9.650,00 | ||||
| 5 | F25-3004 | KL-014 | €27,300.00€ 27.300,00 | ||||
| 6 | F25-3005 | KL-031 | €5,200.00€ 5.200,00 | ||||
| 7 | F25-3006 | KL-027 | €14,100.00€ 14.100,00 | ||||
| 8 | F25-3007 | KL-014 | €18,900.00€ 18.900,00 | ||||
| 9 | F25-3008 | KL-031 | €7,750.00€ 7.750,00 | ||||
| 10 |
Swipe sideways to see all columns
Formula in F3
=IF(COUNTIF(B2:B9,F1) = 0, "Customer not found", IF(OR(F2 < 1, F2 > COUNTIF(B2:B9, F1)), "Position outside the customer's invoices", LARGE(IF(B2:B9 = F1, C2:C9), F2)))
=IF(COUNTIF(B2:B9;F1) = 0; "Customer not found"; IF(OR(F2 < 1; F2 > COUNTIF(B2:B9; F1)); "Position outside the customer's invoices"; LARGE(IF(B2:B9 = F1; C2:C9); F2)))
Result: €18,900.00€ 18.900,00
Step by step
- Excel reads
F1(KL-014) andF2(2). The outer IF tests COUNTIF(B2:B9, F1) = 0 first: customerKL-014appears inB2,B4,B5andB8, so the count is 4 and the test is FALSE. The customer exists. - The inner IF tests OR(F2 < 1, F2 > 4), which is OR(2 < 1, 2 > 4): both parts are FALSE. The position is valid, so Excel takes the last branch, the LARGE.
- Inside it, the inner IF(B2:B9 = F1, C2:C9) compares all eight customers with
KL-014at once. The result is TRUE, FALSE, TRUE, TRUE, FALSE, FALSE, TRUE, FALSE. - Where the test is TRUE the IF passes the amount on, and where it is FALSE (no third argument) it passes FALSE. The array becomes 12400, FALSE, 9650, 27300, FALSE, FALSE, 18900, FALSE.
- LARGE skips the FALSE values and ranks the four numbers: 27300, 18900, 12400, 9650.
- Position 2 is 18900 (
F25-3007), soF3shows 18900. - Change
F2to 5 or to 0: the OR is TRUE andF3shows "Position outside the customer's invoices". TypeKL-099inF1: the count is 0 andF3shows "Customer not found". Without the two tests LARGE would return #NUM!.
Watch out: Wrapping the whole formula in IFERROR gives a friendly message too, but it puts a broken range, a mistyped customer and a missing array entry behind the same text. Here the first test reports "Customer not found" on its own, so a typo in F1 is not mistaken for a position that is too high.
More on this example: why it works, 1 more pitfall, 2 variations
Why it works
LARGE ignores logical values in an array, so the inner IF can blank out every other customer's invoices without removing rows. The two COUNTIF tests check the customer and the position before LARGE runs, so each wrong input gets its own message. The error is prevented at its cause and not hidden afterwards.
More to watch out for
In Excel 2019 and older the formula must be confirmed with Ctrl+Shift+Enter. Without it,
B2:B9=F1is cut down to the single row the formula sits in (implicit intersection), the IF returns FALSE or one amount, and LARGE gives #NUM! for k = 2.The error is visible, but it looks like a wrong k: check the braces around the formula in the formula bar first. Microsoft 365 and Excel 2021 need no key combination.
Variations
=AGGREGATE(14,6, C2:C9 / (B2:B9 = F1), F2)
=AGGREGATE(14;6; C2:C9 / (B2:B9 = F1); F2)
Function 14 is LARGE and option 6 ignores errors. Dividing by FALSE gives #DIV/0! for the other customers, which AGGREGATE skips, and it needs no Ctrl+Shift+Enter in older versions. Prefer it there; it still gives #NUM! for a k that is too high.
=SUM(LARGE(C2:C9,{1, 2, 3}))
=SUM(LARGE(C2:C9;{1\ 2\ 3}))
With an array constant for k, LARGE returns three values at once (30800, 27300, 18900) and SUM adds them: 77000. This is the total of the three largest invoices across all customers.
MasterExcel 2021 or later (This example needs Excel 2021 or later)
Share of the population covered by the n largest invoices
Before choosing key items for Veldhuis Installatie BV you want to know what share of the invoice population the n largest invoices cover; n is typed in E1 and may change during the planning discussion. If someone enters an n above the number of invoices, the formula must cover 100% and not fail.
| Row | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Invoice no. | Amount | n (largest invoices) | 33 | ||
| 2 | F25-4101 | €3,600.00€ 3.600,00 | Coverage | 75.6%75,6%=LET( amounts, B2:B9, n, MIN(N(E1), COUNT(amounts)), IF(n < 1, "Enter an n of at least 1", SUM(LARGE(amounts, SEQUENCE(n))) / SUM(amounts)) )=LET( amounts; B2:B9; n; MIN(N(E1); COUNT(amounts)); IF(n < 1; "Enter an n of at least 1"; SUM(LARGE(amounts; SEQUENCE(n))) / SUM(amounts)) ) | ||
| 3 | F25-4102 | €31,200.00€ 31.200,00 | ||||
| 4 | F25-4103 | €650.00€ 650,00 | ||||
| 5 | F25-4104 | €11,400.00€ 11.400,00 | ||||
| 6 | F25-4105 | €17,850.00€ 17.850,00 | ||||
| 7 | F25-4106 | €1,750.00€ 1.750,00 | ||||
| 8 | F25-4107 | €8,650.00€ 8.650,00 | ||||
| 9 | F25-4108 | €4,900.00€ 4.900,00 | ||||
| 10 |
Swipe sideways to see all columns
Formula in E2
=LET( amounts,B2:B9, n, MIN(N(E1), COUNT(amounts)), IF(n < 1, "Enter an n of at least 1", SUM(LARGE(amounts, SEQUENCE(n))) / SUM(amounts)) )
=LET( amounts;B2:B9; n; MIN(N(E1); COUNT(amounts)); IF(n < 1; "Enter an n of at least 1"; SUM(LARGE(amounts; SEQUENCE(n))) / SUM(amounts)) )
Result: 75.6%75,6%
Step by step
- LET binds its names in order. amounts becomes
B2:B9, eight numbers adding up to 80000. - n becomes MIN(N(E1), COUNT(amounts)) = MIN(3, 8) = 3. N turns an empty
E1into 0 (a number stays as it is), and the MIN caps n at the number of invoices. - The last line of LET is the IF. It tests n < 1: 3 < 1 is FALSE, so the calculation runs. For n = 0 or an empty
E1(N makes it 0) the IF returns the text instead, because SEQUENCE(0) would give an error. - SEQUENCE(3) gives the positions 1, 2 and 3.
- LARGE(amounts, {1;2;3}) returns one value per position: 31200 (
F25-4102), 17850 (F25-4105) and 11400 (F25-4104). - SUM adds those three: 31200 + 17850 + 11400 = 60450.
- The IF then divides by SUM(amounts): 60450 / 80000 = 0.755625, which the percent format shows as 75.6%.
- Change
E1to 10: n becomes MIN(10, 8) = 8, LARGE ranks all eight amounts and the result is 100%. Without the MIN, position 9 would give #NUM!.
Watch out: Do not use this construction to choose the items: it returns a share and not the invoices. When you need the invoice numbers, use TAKE(SORT(A2:B9, 2, -1), n). It shows which invoices they are and keeps equal amounts as separate rows.
More on this example: why it works, 2 more pitfalls, 1 variation
Why it works
Giving LARGE an array of positions turns one call into a ranked list, and SUM collapses that list into one figure. LARGE with SEQUENCE needs only the column of amounts and no sorted table, which suits a share that is shown in one cell; when you also show the invoices, TAKE with SORT gives the same figure (see the variation).
LET names each step, so the cap on n and the guard are visible and the final division reads as plain arithmetic.
More to watch out for
- The mirror-image mistake: SMALL instead of LARGE still returns a believable percentage. SUM(SMALL(amounts, SEQUENCE(3))) gives 650 + 1750 + 3600 = 6000, so 6000 / 80000 = 7.5%, and nothing in the sheet signals that you ranked from the wrong end.
- Credit notes in the amounts are negative. They reduce SUM(amounts) but never count among the largest, so the coverage can look better than it is. Test the population for negatives first.
Variations
=SUM(TAKE(SORT(B2:B9,1, -1), E1)) / SUM(B2:B9)
=SUM(TAKE(SORT(B2:B9;1; -1); E1)) / SUM(B2:B9)
Gives the same 75.6%. TAKE returns what exists when n is too high, so it needs no MIN. Prefer it on Excel 2024 or Microsoft 365 when you will also show the invoices themselves.
Common mistakes
| Symptom | Cause | Fix |
|---|---|---|
| #NUM! when you pass an amount as k | Passing a value instead of a position, for example LARGE(B2:B7, 15600) to find that amount, and getting #NUM!. | To find where a known value sits, use MATCH or XMATCH. |
| Second largest equals the largest | Expecting LARGE(range, 2) to skip a duplicate of the largest value. With two equal maxima it returns the maximum again. | For the second largest distinct value, apply UNIQUE first: LARGE(UNIQUE(range), 2). |
| Ranking built on fewer numbers than rows | Amounts that were imported as text are skipped without a warning, so the ranking is built on fewer numbers than you think. | Compare COUNT(range) with the number of rows. If they differ, convert the text to numbers first. |
| Large credit notes missing from the top | Using LARGE to find the biggest credit notes. Credit notes are negative, so they rank at the bottom, however large they are in absolute terms. | Rank the absolute values instead: LARGE(ABS(range), k) as an array formula, or use SMALL to reach the most negative amounts. |
Used in combinations
- The five largest manual entries with TAKE, SORT and FILTERList 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.Excel 2024 or later (This combination needs Excel 2024 or later)