ThisCalculator

Everyday

Nth Largest Value with Criteria

Updated · Standard formula, shown below

The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.

This calculator finds the Nth largest value in a list of numbers, but only among the entries that meet a condition you set. You enter the list, the rank N, and a criteria expression, and it returns the Nth largest matching value. The formula is a combination of array filtering and the LARGE function, which is a standard feature in spreadsheet software like Excel and Google Sheets.

Formula

In Excel or Google Sheets, the formula is:

=LARGE(IF(condition, data_range), N)

For example, if your data is in A1:A10 and you want the 3rd largest value greater than 50, you would enter:

=LARGE(IF(A1:A10>50, A1:A10), 3)

This must be entered as an array formula. In Excel, press Ctrl+Shift+Enter. In Google Sheets, it works automatically. The IF function creates an array of values that meet the condition and FALSE for those that don’t. LARGE then ignores the FALSE entries and returns the Nth largest from the remaining numbers.

Worked Example

Using the default values from the calculator: Data list = 10, 20, 30, 40, 50, 60, 70, 80, 90, 100; N = 3; Criteria = >50.

  1. Filter the list to values greater than 50: 60, 70, 80, 90, 100.
  2. Sort these in descending order: 100, 90, 80, 70, 60.
  3. The 3rd largest is 80.

The calculator returns 80, matching the formula result.

How to Choose the Right Criteria

Your criteria can be a simple comparison like >50, <=100, or ="Yes". For text, use quotes. For dates, use DATE(2025,1,1) or a cell reference. You can also combine conditions with AND or OR, but that requires a more complex array formula. In Excel, you might use =LARGE(IF((range>50)*(range<100), range), N) for an AND condition.

Common Mistakes and Pitfalls

  • Forgetting to press Ctrl+Shift+Enter in Excel – the formula will return a #VALUE! error.
  • Using a criteria that matches no values – LARGE will return #NUM!.
  • Including text or blanks in the data range – they are ignored by LARGE, but may cause issues with IF.
  • N is larger than the number of matching values – same #NUM! error.
  • Mixing up ascending vs descending – LARGE always uses descending order; use SMALL for ascending.

How to Read the Result

The result is the Nth largest value among those that meet your criteria. For example, if you want the second-highest sales figure for the East region, you’d set criteria to region="East" and N=2. The number returned is that exact value, not a rank or position. If you need the position instead, use MATCH.

Limitations and Alternatives

This formula works best with a single condition. For multiple conditions or more complex logic, consider using the FILTER function (Excel 365, Google Sheets) combined with LARGE: =LARGE(FILTER(range, condition1 * condition2), N). This is more readable and doesn’t require array entry. For very large datasets, array formulas can slow down performance; consider using a pivot table or a helper column instead.

Frequently Asked Questions

How do I find the Nth largest value with multiple criteria?

Use the FILTER function with multiplication for AND logic: =LARGE(FILTER(data, (criteria1)*(criteria2)), N). For OR logic, use addition: (criteria1)+(criteria2).

What does #NUM! mean?

It means either N is larger than the number of values that meet the criteria, or there are no matching values at all. Check your criteria and N.

Can I use this with text criteria?

Yes, use quotes around text, e.g., ="Yes". For partial matches, use wildcards like *text* in the criteria, but that requires a different approach with SEARCH or FIND.

How do I get the Nth smallest instead?

Replace LARGE with SMALL. The syntax is identical: =SMALL(IF(condition, range), N).

Does this work in Google Sheets?

Yes, but you don’t need Ctrl+Shift+Enter. The formula works as an array formula automatically. FILTER also works in Google Sheets.

Can I use this with dates?

Yes, dates are stored as numbers. Use a criteria like >DATE(2025,1,1) or reference a cell containing the date.

Related

Formula used
in list | Data list | - | 10, 20, 30, 40, 50, 60, 70, 80, 90, 100 | 0
in n | Rank (N) | - | 3 | 1
in crit | Criteria (e.g., >50) | - | >50 | 0
out result | Nth largest matching value | - | nth_largest(list, n, crit) | 2

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.