LARGE returns the k-th largest value from a set of numbers, so you can pull the top score, second-best sales figure, or third-highest price without sorting the data. It takes a range or array and a rank number k, and gives back the value at that position from the top.
=LARGE(array, k)What does LARGE do?
LARGE is a statistical function that answers a simple question: what is the k-th largest number in this list? You give it a range of numbers and a rank such as 1, 2, or 3, and it returns the value that sits at that position when the numbers are sorted from highest to lowest. =LARGE(A2:A10,1) gives the maximum, =LARGE(A2:A10,2) gives the second highest, and so on.
This matters because most real questions are about position, not totals. You often need the top performer, the runner-up, the third-highest invoice, or the best result excluding an outlier. Sorting a sheet changes the order of your data and can break other formulas or reports. LARGE lets you pull ranked values in place, so your table stays intact and your answers update automatically when the data changes.
A typical situation: a sales manager has a column of monthly totals and wants to highlight the top three regions in a summary box. Instead of sorting the list and copying values, they use =LARGE(B2:B20,1), =LARGE(B2:B20,2), and =LARGE(B2:B20,3) in three cells. When new sales figures are entered, the summary updates immediately, and the original table keeps its original order.
When to use LARGE
- Finding the highest, second-highest, or third-highest score in a class or team.
- Reporting the top N sales figures without sorting the source table.
- Identifying the largest invoice, expense, or order value for review.
- Comparing a value against the 3rd or 5th largest in a dataset.
- Building a dynamic top-three summary that updates when data changes.
- Extracting the best result while ignoring zeros or blanks with helper logic.
LARGE syntax
=LARGE(array, k)| Argument | Required? | What to enter |
|---|---|---|
array | Required | The range or list of numbers to search. Example: B2:B10 or {10,20,30}. |
k | Required | The position from the largest, where 1 means the largest, 2 means the second largest, and so on. Example: 2. |
What it returns: LARGE returns a single number: the k-th largest value in the array. If k is 1, it returns the maximum. If k is larger than the count of numbers, it returns #NUM!.
LARGE examples
1 Example 1: Second-highest score in a small list
The goal is to find the second-highest score from a short list of student results. The formula =LARGE(B2:B6,2) looks at the scores in B2:B6 and returns the value in the second position when sorted from largest to smallest. The scores are 88, 92, 79, 95, and 85, so the largest is 95 and the second largest is 92. The formula returns 92.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Score | |||
| 2 | Ana | 88 | 92 | ||
| 3 | Ben | 92 | |||
| 4 | Cara | 79 | |||
| 5 | Dan | 95 | |||
| 6 | Eve | 85 | |||
| 7 |
2 Example 2: Top three monthly sales figures
A small team records monthly sales in column B. The manager wants the top three values in a summary area without sorting the table. =LARGE(B2:B7,1) returns the highest value, 1500. =LARGE(B2:B7,2) returns the second highest, 1400. =LARGE(B2:B7,3) returns the third highest, 1300. The formula in D2 uses k=3 to return the third-largest figure, 1300.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Sales | |||
| 2 | Jan | 1200 | 1300 | ||
| 3 | Feb | 1500 | |||
| 4 | Mar | 900 | |||
| 5 | Apr | 1400 | |||
| 6 | May | 1300 | |||
| 7 | Jun | 1100 | |||
| 8 |
3 Example 3: Highest invoice amount for review
An accounts payable clerk needs the largest invoice amount from a list to flag it for approval. The formula =LARGE(D2:D8,1) looks at the invoice amounts in D2:D8 and returns the largest value. The amounts are 450, 1200, 300, 800, 1500, 600, and 950, so the largest is 1500. The formula returns 1500.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Invoice | Vendor | Date | Amount | |||
| 2 | INV-001 | Alpha | 2024-01-05 | 450 | 1500 | ||
| 3 | INV-002 | Beta | 2024-01-12 | 1200 | |||
| 4 | INV-003 | Gamma | 2024-01-18 | 300 | |||
| 5 | INV-004 | Delta | 2024-02-02 | 800 | |||
| 6 | INV-005 | Epsilon | 2024-02-10 | 1500 | |||
| 7 | INV-006 | Zeta | 2024-02-15 | 600 | |||
| 8 | INV-007 | Eta | 2024-02-20 | 950 | |||
| 9 |
4 Example 4: Third-highest price in a product list
A purchasing assistant wants to know the third-highest unit price among products to compare against a new quote. The formula =LARGE(C2:C9,3) examines the prices in C2:C9 and returns the value in the third position from the top. The prices are 12.50, 18.00, 9.99, 22.00, 15.75, 30.00, 11.20, and 25.50, so the top three are 30.00, 25.50, and 22.00. The formula returns 22.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | |||
| 2 | Widget | Hardware | 12.5 | 22 | ||
| 3 | Gadget | Hardware | 18 | |||
| 4 | Cable | Accessory | 9.99 | |||
| 5 | Monitor | Display | 22 | |||
| 6 | Keyboard | Accessory | 15.75 | |||
| 7 | Printer | Hardware | 30 | |||
| 8 | Mouse | Accessory | 11.2 | |||
| 9 | Scanner | Hardware | 25.5 | |||
| 10 |
5 Example 5: Second-highest regional revenue
A regional manager has revenue figures for six regions and wants the second-highest figure for a comparison report. The formula =LARGE(E2:E7,2) looks at the revenue values in E2:E7 and returns the second-largest value. The revenues are 5000, 7500, 6200, 4800, 9000, and 7100, so the largest is 9000 and the second largest is 7500. The formula returns 7500.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Region | Q1 | Q2 | Q3 | Revenue | |||
| 2 | North | 1200 | 1300 | 1400 | 5000 | 7500 | ||
| 3 | South | 1500 | 1600 | 1700 | 7500 | |||
| 4 | East | 1100 | 1200 | 1300 | 6200 | |||
| 5 | West | 900 | 1000 | 1100 | 4800 | |||
| 6 | Central | 1800 | 1900 | 2000 | 9000 | |||
| 7 | Northeast | 1400 | 1500 | 1600 | 7100 | |||
| 8 |
Common LARGE errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #NUM! | k is larger than the number of numeric values in the array, or k is zero or negative. | Make sure k is between 1 and the count of numbers. Use =COUNT(range) to check how many numbers exist. |
| #VALUE! | k is not a number, for example text or a blank cell reference. | Enter a numeric value for k, or reference a cell that contains a number. |
| #N/A or unexpected result | The array contains text or errors that interfere with ranking, or the range is wrong. | Use a clean numeric range. Remove error values or wrap with IFERROR if needed. |
| Wrong value returned | k is confused with position in the original list rather than rank from the top. | Remember k=1 is the largest, k=2 is second largest, and so on. Use =LARGE(range,1) for the maximum. |
Tips for using LARGE
- Use
=LARGE(range,1)as a quick maximum when you do not want to use MAX. - Combine LARGE with ROW or ROWS to build a dynamic top-N list:
=LARGE(B2:B20,ROW(A1))filled down. - LARGE ignores text, blanks, and logical values in the array, so keep the range numeric for predictable results.
- To get the smallest values instead, use SMALL with the same syntax.
- If you need the top value with criteria, use LARGE with an array formula or the newer FILTER and SORT functions.
- Format the result cell as a number or currency to match the source data.
Frequently asked questions
What is the difference between LARGE and MAX?
MAX returns the single highest value. LARGE returns the k-th highest, so =LARGE(range,1) equals MAX, but =LARGE(range,2) gives the second highest.
Can LARGE handle duplicate values?
Yes. Duplicates are ranked separately. If the values are 10, 10, and 5, then =LARGE(range,1) and =LARGE(range,2) both return 10, and =LARGE(range,3) returns 5.
Why does LARGE return #NUM!?
The most common reason is that k is greater than the number of numeric values, or k is zero or negative. Check the count of numbers and set k within that range.
Does LARGE work with dates?
Yes, because Excel stores dates as numbers. =LARGE(A2:A10,1) returns the latest date as a serial number; format the result cell as a date to display it correctly.
How do I get the top three values in separate cells?
Enter =LARGE(range,1), =LARGE(range,2), and =LARGE(range,3) in three cells, or use =LARGE(range,ROW(A1)) and fill down three rows.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. LARGE reference