Find Max N Values in a Row and Return Headers in Google Sheets

Published on

When finding the max N values in a row and returning their headers in Google Sheets, consider the following:

  • The headers should follow the order of the max values, meaning the first header corresponds to the largest value, the second header to the second-largest value, and so on.
  • The formula should return either the headers of the largest N values or the headers of the unique N largest values, depending on your requirement.

For example, consider the following data:

ItemABCDE
Value202081215
  • Headers of the largest 3 values: {"Item A", "Item B", "Item E"} corresponding to 20, 20, and 15.
  • Headers of the unique 3 largest values: {"Item A, Item B" | "Item E" | "Item D"} corresponding to 20, 15, and 12.

Below, we explore two formulas for each case.

Find Max N Values in a Row and Return Their Headers

The sample data consists of sales dates in Column A, item names in B1:F1 , and their corresponding sales quantities in the rows below.

Headers of the top 3 maximum values in each row in Google Sheets

To get the headers of the top 3 values in each row, use the following formula in cell H2 and drag it down:

=TEXTJOIN(", ", TRUE, UNIQUE(MAP(SORTN(TOCOL(B2:F2, 0), 3, 0, 1, FALSE), LAMBDA(r_, IFNA(FILTER($B$1:$F$1, B2:F2=r_, B2:F2>0))))))

Where:

  • $B$1:$F$1 is the header row reference.
  • B2:F2 is the range of quantities.
  • 3 in SORTN(..., 3, 0, 1, FALSE) determines the number of maximum values to return.

When you drag the formula down, the header row reference remains the same (due to absolute referencing), while the quantity row reference adjusts for each row. This allows the formula to return the headers of the top 3 values for each row.

How the Formula Works:

  • TOCOL(B2:F2 , 0): Converts the row values into a column and removes empty cells.
  • SORTN(..., 3, 0, 1, FALSE): Sorts the values in descending order and returns the top 3 values.
  • MAP(..., LAMBDA(r_, IFNA(FILTER($B$1:$F$1, B2:F2=r_, B2:F2>0)))):
    • Filters the headers where the quantity matches the current value in the SORTN result and is greater than 0.
    • The MAP function applies this logic to each value in the SORTN result.
  • UNIQUE(...): Removes duplicate headers corresponding to multiple occurrences of the same maximum value.
  • TEXTJOIN(", ", TRUE, ...): Combines the headers into a single cell, separated by commas.

This is how the formula finds the headers of the top N values in a row.

Array Formula

If you don’t want to drag the formula down and instead want it to automatically expand, use the BYROW function to apply the formula to each row in the range:

=BYROW(B2:F, LAMBDA(er_, TEXTJOIN(", ", TRUE, UNIQUE(MAP(SORTN(TOCOL(er_, 0), 3, 0, 1, FALSE), LAMBDA(r_, IFNA(FILTER(B1:F1, er_=r_, er_>0))))))))

Here, the drag-down formula is converted into a custom LAMBDA function:

LAMBDA(er_, TEXTJOIN(", ", TRUE, UNIQUE(MAP(SORTN(TOCOL(er_, 0), 3, 0, 1, FALSE), LAMBDA(r_, IFNA(FILTER(B1:F1, er_=r_, er_>0)))))))

The BYROW function applies this lambda function to each row in the range B2:F.

Find Unique Max N Values in a Row and Return Their Headers

Using the same sample data, here’s the drag-down formula to find the headers of the unique top 3 values. Place it in cell H2:

=TEXTJOIN(" | ", TRUE, MAP(SORTN(TOCOL(B2:F2, 0), 3, 2, 1, FALSE), LAMBDA(r_, TEXTJOIN(", ", TRUE, IFNA(FILTER($B$1:$F$1, B2:F2=r_, B2:F2>0))))))
Examples of Finding Max N Unique Values in a Row and Returning Headers in Google Sheets

Where:

  • $B$1:$F$1 is the header row reference.
  • B2:F2 is the row reference containing the quantities.
  • 3 in SORTN(..., 3, 2, 1, FALSE) determines the number of unique maximum values to return.

This formula displays the results in a user-friendly format:

  • Headers corresponding to the same maximum value are comma-separated.
  • Headers corresponding to different maximum values are pipe-separated.

For example:

Apple, Orange | Banana | Pears
  • Apple and Orange correspond to the first maximum value.
  • Banana corresponds to the second maximum value.
  • Pears corresponds to the third maximum value.

How the Formula Works:

  • TOCOL(B2:F2 , 0): Converts the row values into a column and removes empty cells.
  • SORTN(..., 3, 2, 1, FALSE): Sorts the values in descending order, removes duplicates, and returns the top 3 unique values.
  • MAP(..., LAMBDA(r_, TEXTJOIN(", ", TRUE, IFNA(FILTER($B$1:$F$1, B2:F2=r_, B2:F2>0))))):
    • Filters the headers where the quantity matches the current value in the SORTN result and is greater than 0.
    • Combines multiple headers (if any) into a single cell, separated by commas.
  • TEXTJOIN(" | ", TRUE, ...): Combines the results for each unique maximum value into a single cell, separated by pipes.

Array Formula for Unique Max N Values

To avoid dragging the formula down, use the BYROW function to create an array formula:

=BYROW(B2:F, LAMBDA(er_, TEXTJOIN(" | ", TRUE, MAP(SORTN(TOCOL(er_, 0), 3, 2, 1, FALSE), LAMBDA(r_, TEXTJOIN(", ", TRUE, IFNA(FILTER(B1:F1, er_=r_, er_>0))))))))

This formula applies the same logic as the drag-down version but automatically processes each row in the range B2:F.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

1 COMMENT

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.