LARGE IF formula in Excel: get n-th highest value with criteria

This tutorial demonstrates how to find the largest numbers in Excel datasets, especially when needing to filter results based on one or more criteria. Since Excel lacks a dedicated LARGEIF function, this guide provides formulas to achieve this functionality.

Excel's LARGE IF Formula

To find the nth largest value meeting specific criteria, combine the LARGE and IF functions:

{=LARGE(IF(criteria_range=criteria, values), n)}

This is an array formula (in pre-dynamic array Excel versions, press Ctrl Shift Enter). n represents the desired rank (1st, 2nd, 3rd, etc.). In Excel 365 and later, simply press Enter.

Example: Finding the top 3 scores in a "Science" subject:

=LARGE(IF($B$2:$B$15=F$2, $C$2:$C$15), $E3)

Where:

• $B$2:$B$15: Subject column (criteria_range).
• F$2: The subject "Science" (criteria).
• $C$2:$C$15: Score column (values).
• $E3: The rank (n).

To avoid manual input of n, use ROWS:

=LARGE(IF($B$2:$B$15=E$2,$C$2:$C$15), ROWS(A$2:A2))

This dynamically adjusts n as you copy the formula down.

To handle cases where no matching value is found (resulting in a #NUM! error), use IFERROR:

=IFERROR(LARGE(IF($B$2:$B$15=$F$2, $C$2:$C$15), $E3), "Not found")

Multiple Criteria (AND Logic)

For multiple conditions (AND logic), nest IF statements:

{=LARGE(IF(criteria_range1=criteria1, IF(criteria_range2=criteria2, values)), n)}

Alternatively, multiply the criteria:

{=LARGE(IF((criteria_range1=criteria1)*(criteria_range2=criteria2), values), n)}

Both are array formulas (unless using Excel 365 or later).

Multiple Criteria (OR Logic)

For multiple conditions (OR logic), add the criteria expressions:

{=LARGE(IF((criteria_range1=criteria1) (criteria_range2=criteria2), values), n)}

Filtering Top N Values (Excel 365 )

Excel 365 offers simpler solutions using FILTER and SORT:

One Condition:

SORT(FILTER(values, (values>=LARGE(IF(criteria_range=criteria, values), n))*(criteria_range=criteria)), 1, -1)

Multiple AND Criteria:

SORT(FILTER(values, (values>=LARGE(IF((criteria_range1=criteria1)*(criteria_range2=criteria2), values), n))*(criteria_range1=criteria1)*(criteria_range2=criteria2)), 1, -1)

Multiple OR Criteria:

SORT(FILTER(values, (values>=LARGE(IF((criteria_range1=criteria1) (criteria_range2=criteria2), values), n))*((criteria_range1=criteria1) (criteria_range2=criteria2))), 1, -1)

These formulas directly spill the results into the worksheet.

Filtering the Nth Largest Value (Excel 365 )

For finding a specific ranked value, use LARGE with FILTER:

One Condition:

LARGE(FILTER(values, criteria_range=criteria), n)

Multiple AND Criteria:

LARGE(FILTER(values, (criteria_range1=criteria1)*(criteria_range2=criteria2)), n)

Multiple OR Criteria:

LARGE(FILTER(values, (criteria_range1=criteria1) (criteria_range2=criteria2)), n)

This tutorial provides various methods for finding largest values with conditions in Excel, catering to different Excel versions and complexity levels. Remember to adjust cell references to match your data.

The above is the detailed content of LARGE IF formula in Excel: get n-th highest value with criteria. For more information, please follow other related articles on the PHP Chinese website!