This tutorial demonstrates how to efficiently locate the top N values within a dataset and retrieve associated data using Excel formulas. Whether you need the highest, lowest, or those meeting specific criteria, this guide provides solutions.

• Finding Top/Bottom Values
• Locating Top Values in Rows
• Retrieving Matches for Largest/Smallest Values
• Handling Duplicates in Top Value Searches
• Incorporating Criteria for Top Value Selection
• Utilizing the FILTER Function for Top N Values

Excel Formulas for Top 3, 5, 10, or N Values

To identify the highest N values, combine the LARGE and ROWS functions:

=LARGE(values, ROWS(A$2:A2))

For instance, to find the top N values in cells B2:B12, enter this formula in the desired output cell (e.g., D2), then drag down to cover the required number of cells (3 for top 3, 5 for top 5, etc.).

The LARGE function identifies the k-th largest value, while ROWS(A$2:A2) dynamically generates the 'k' value (1, 2, 3...). The absolute reference A$2 keeps the starting row constant, while the relative reference A2 increments with each cell copy.

Excel Formulas for Bottom N Values

To find the lowest N values, use the SMALL function instead of LARGE:

=SMALL(values, ROWS(A$2:A2))

Finding Top N Values in a Row

For horizontally arranged data, replace ROWS with COLUMNS:

=LARGE(values, COLUMNS(A$2:A2)) (for top values) =SMALL(values, COLUMNS(A$2:A2)) (for bottom values)

Retrieving Data Associated with Top N Values

Use INDEX and MATCH with LARGE (or SMALL) to retrieve data corresponding to top N values:

=INDEX(return_array, MATCH(LARGE(lookup_array, k), lookup_array, 0))

Alternatively, Excel 365 users can simplify this with XLOOKUP:

=XLOOKUP(LARGE(lookup_array, k), lookup_array, return_array)

Handling Duplicate Top Values

For datasets with duplicates, a more robust MATCH function is needed (array formula, Ctrl Shift Enter in pre-365 versions):

=INDEX(return_array, MATCH(1, (lookup_array=LARGE(lookup_array, k))*(COUNTIF(output_range, return_array)=0), 0))

Excel 365 offers a simpler solution using FILTER.

Finding Top Values with Criteria

Use IF statements within the LARGE and MATCH formulas to incorporate criteria:

=LARGE(IF(criteria, values), k)

Again, Excel 365 simplifies this with FILTER.

Using the FILTER Function (Excel 365)

Excel 365's FILTER and SORT functions provide a streamlined approach:

=SORT(FILTER(data, numbers>=LARGE(numbers, n)), sort_index, -1) (Top N) =SORT(FILTER(data, numbers (Bottom N)

This tutorial provides comprehensive methods for extracting top N values in Excel, catering to various scenarios and Excel versions. A practice workbook is available for download.

The above is the detailed content of Excel formula to find top 3, 5, 10 values in column or row. For more information, please follow other related articles on the PHP Chinese website!