To extract the column header of a maximum value in Google Sheets, we often use a combination of HLOOKUP and MAX formulas. However, when we want to repeat this process for every row in a table, things can get a little complicated. The HLOOKUP formula can return results in multiple rows, but the MAX formula cannot. Therefore, using HLOOKUP alone is not an option.
But don’t worry! I have a solution for you. In this post, I will share an array formula that will enable you to return the column header of the maximum value in every row in Google Sheets. So, let’s dive in!
Using a Drag-Down Formula with MAX and HLOOKUP
Let’s say we have a trading company with three warehouses, and we want to keep track of the quantity of each item stored in each warehouse. We can easily find the maximum quantity of each item and determine which warehouse it is stored in using the column header.
To do this, we can use the following non-array formulas:
In cell F2, enter the MAX formula to find the maximum value (qty) in that row:
=max(B2:D2)In cell G2, enter the HLOOKUP formula to return the header (warehouse number) corresponding to the maximum value (qty):
=hlookup(F2,{A2:D2;$A$1:$D$1},2,0)You can then copy and paste or drag both formulas down to get the column header of the maximum value in every row in Google Sheets.
Note: If you don’t want to see the maximum numbers in column F, you can include the F2 formula within the HLOOKUP formula as follows:
=hlookup(max(B2:D2),{A2:D2;$A$1:$D$1},2,0)An Array Formula for Extracting the Column Header of the Maximum Value in Google Sheets
I know you’re here for the array formula that can extract the column header of maximum values in every row of a table. This formula solves two obstacles: expanding the MAX and HLOOKUP formulas. Let’s break it down into two parts for better understanding.
Part I: Solution Using DMAX Formula
To expand the maximum result, we can use another formula called DMAX. Here is how you can do it:
- Empty the cells in column F starting from F1.
- Enter the following DMAX formula in cell F1:
={"Max"; ArrayFormula(if(len(A2:A), DMAX(transpose({{"Row";sequence(rows(A2:A1000),1)}, B1:D1000}), sequence(1000,1,2), transpose({"Row",B1:D1})),))}- This formula covers rows 2 to 1000. If you have more records, make sure to adjust the cell range accordingly (e.g., A2:A2000 and D2000).
Please note that with a large dataset, this array formula may cause performance issues. Keep that in mind.
Part II: Array Formula to Extract the Column Header of the Maximum Value
Similar to Part I, empty the cells in column G starting from G1. Then enter the following formula in cell G1:
={"Header"; ArrayFormula(ifna(if(len(F2:F1000), vlookup(row(A2:A1000)&"~"&F2:F1000, Query(split(flatten(row(A2:A1000)&"~"&B2:D1000&"🐟"&B1:D1), "🐟"), "Select * where Col1 is not null and Col2 is not null"),2,0),)))}This formula will return the column header of the maximum values in Google Sheets for rows up to 1000. Please adjust the range if you have more rows.
Now, you might be wondering how this formula works. Let’s explain it further.
Formula Explanation for Part II
To better understand the formula, let’s limit the expansion to row 6. This way, we can remove unnecessary elements and shorten the formula.
Here is the formula that returns the column header of maximum values from rows 2 to 6 (without the field label). Insert it in cell G2 for testing purposes:
=ArrayFormula(vlookup(row(A2:A6)&"~"&F2:F6, Query(split(flatten(row(A2:A6)&"~"&B2:D6&"🐟"&B1:D1), "🐟"), "Select * where Col1 is not null and Col2 is not null"),2,0))Let’s break down the syntax and arguments used in the VLOOKUP formula:
Syntax:
VLOOKUP(search_key, range, index, [is_sorted])Arguments:
- Search_key:
row(A2:A6)&"~"&F2:F6
This formula adds the row numbers with the maximum values in column F. The tilde sign serves as a separator between the row numbers and the maximum values.
Range: Query(split(flatten(row(A2:A6)&"~"&B2:D6&"🐟"&B1:D1), "🐟"), "Select * where Col1 is not null and Col2 is not null")
This range is the core of the formula. It adds row numbers to each value in columns B2 to D6, using the tilde separator. Additionally, it includes an extra column that contains the corresponding column headers.
The FLATTEN function is used to unpivot the data in the range B2:D6. You can find more information on how to unpivot a dataset in Google Sheets here.
The QUERY function is not mandatory. You can use FLATTEN on its own:
=ArrayFormula(split(flatten(row(A2:A6)&"~"&B2:D6&"🐟"&B1:D1), "🐟"))The QUERY function is included to remove blank values when using a larger range (e.g., A2:A1000 and B2:D1000).
Index: 2
Is_sorted: 0 (the range is not sorted)
The VLOOKUP formula searches for the search_key in the specified range and returns the matching value from the second column (index #2).
Lambda to Return the Column Header of the Maximum Value in Every Row
The formula explained above has two disadvantages. It can be slightly complex, but that is acceptable since it works in every row. However, it does not support returning multiple warehouse names when there are multiple maximum values for an item.
To overcome this limitation, I have an excellent array formula that works regardless of whether there is a single or multiple maximum values.
Enter the following formula in cell I2 of an empty column I:
=byrow(B2:D,lambda(row, if(counta(row)=0,,join(", ",filter($B$1:$D$1,row=max(row))))))This BYROW lambda helper formula will return the column headers of maximum values in every row.
Here’s how the formula works:
- The formula uses the name “row” in the lambda formula to reference the rows in the range B2:D.
- For each row, it finds the maximum value using the MAX function.
- It then filters the column headers based on the maximum value using the FILTER function.
- Finally, it joins the filtered column headers using a comma separator.
With this BYROW lambda formula, you can easily extract the column headers of maximum values in every row in Google Sheets.
That’s it! Now you know how to use array formulas to extract the column header of maximum values in Google Sheets. Feel free to explore the resources below for more information on lookup formulas and data analysis in Google Sheets.
Resources:
