To return the column header of a max value in Google Sheets, we can use an Hlookup formula combined with Max. But what about when you want to repeat the same in every row on your table?

Of course, the Hlookup can return results in multiple rows, but at the same time, the Max can’t. To further complicate the problem, we require the output from the header row. That fades away the hope of using Hlookup.

To return column headers of max values in every row in Google Sheets, do we require to depend on a drag-down formula?

Nope! I have an array formula to return the column header of max value in Google Sheets. In this post, I am going to share the same with you. First, let me start with a drag-down formula.

Using Drag-Down Formula that Involves Max and Hlookup

Assume a trading company has three warehouses and maintains the item-wise stocks in a simple Google Spreadsheet. Column A contains the item names, and columns B to D contain the quantity stored in each warehouse.

Using this dataset, one can easily find the max quantity of each item kept in which warehouse. We only need to find the column header of the max value.

In the above example, there are only five items in rows 2 to 6.

Here, we can use a non-array formula to return the warehouse name based on the max quantity kept. In cell F2, insert the following MAX formula to return the max value (qty) in that row.

=max(B2:D2)

In cell G2, insert the following HLOOKUP to return the header (warehouse number) corresponding to the max value (qty).

=hlookup(F2,{A2:D2;$A$1:$D$1},2,0)

Copy-paste or drag both formulas down to get the column header of the max value in every row in Google Sheets.

Note:- If you don’t want to see the max numbers in column F, include the F2 formula within Hlookup as below.

=hlookup(max(B2:D2),{A2:D2;$A$1:$D$1},2,0)

Array Formula to Return Column Header of Max Value in Google Sheets

I know, most probably, you are here for the array formula to get the column header of max values in every row of a table. You may have faced two obstacles – Expanding the above Max and then the Hlookup. Let me call it Part I and Part II for the explanation purpose.

Update:- Added a new lambda solution at the end of this tutorial under the subtitle “Lambda to Return the Max Column Header in Every Row.”

Part I – Solution Using Dmax

To expand the max result, we can use DMAX as explained here – Return First and Second Highest Values in Each Row in Google Sheets. After emptying F1:F, 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})
           ),
        )
     )
}

I have written the formula to cover up to the row number 1000.

When you have more records, please don’t forget to change 1000 in the cell range A2:A1000 and D1000 with 2000 (as per requirement).

Keep in mind; a large dataset may cause performance issues in the above array formula.

Part II – Array Formula to Get the Column Header of Max Value in Google Sheets

Similar to part I, empty G1:G. 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
             ),
           )
        )
     )
}

It would return the column header of max values in Google Sheets (read column header as warehouse numbers) up to row 1000. That means the last paragraph under the above subtitle is also applicable here.

I don’t want to leave you in the dark without sharing how the formula works (we can say formula logic) or how I have combined them and their purpose.

Part II Formula Explanation

The best way to learn the above formula is to limit the formula expansion to row # 6, as it is the last row that contains any value. That act will help us to remove some unwanted strings from the formula and thus shorten it.

Here is the formula that requires to return column header of max values from rows two to six. Since it doesn’t contain a field label, when testing, you should insert it in cell G2, not in cell G1 as above.

=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
     )
)

The above is a VLOOKUP formula. The syntax and the arguments used are as follows.

Syntax

VLOOKUP(search_key, range, index, [is_sorted])

Arguments

Search_key: row(A2:A6)&"~"&F2:F6

This formula simply adds the ROW numbers with the max values in column F. The tilde sign is inserted as the separator between the row numbers and max values.

range – Query(split(flatten(row(A2:A6)&"~"&B2:D6&"🐟"&B1:D1),"🐟"),
"Select * where Col1 is not null and Col2 is not null")

The above output is the soul of the formula that returns column headers of max values in every row in Google Sheets. Take a look at the result.

You can see the formula adds row numbers to every value in B2:D6 (used the same tilde separator). Further, it has an additional column that contains the corresponding column headers.

Actually, I have used the FLATTEN function to unpivot the data in the range B1:D6. You can find more info here – A Simple Formula to Unpivot a Dataset in Google Sheets.

The QUERY is not a must in the above case. You can use Flatten itself as below.

=ArrayFormula(split(flatten(row(A2:A6)&"~"&B2:D6&"🐟"&B1:D1),"🐟"))

The Query is included for removing blanks when we are using the range like A2:A1000 and B2:D1000.

index – 2

is_sorted – 0 (the range is not sorted)

The Vlookup searches the search_key in the above range and would return the matching value from the second column (index # 2).

Lambda to Return the Max Column Header in Every Row

The above formula has two disadvantages.

It’s a slightly complex formula, but that is OK since it works in every row.

The second disadvantage is the most obvious one.

For example, I have the same (max) quantity kept in two or more warehouses for an item. I want all that warehouse names. How do I get them?

The above formula won’t support this kind of output. Here is the best array formula that works irrespective of single or multiple max values.

=byrow(B2:D,lambda(row, if(counta(row)=0,,join(", ",filter($B$1:$D$1,row=max(row))))))

Enter this formula in cell I2 of the empty column I.

This BYROW lambda helper formula will return the max column headers in every row.

How does this formula work?

I’ve used the name row in the above lambda formula to define rows in B2:D (range). In each row, it takes the corresponding row.

1. The ROW formula returns the max value of the first row.
2. The FILTER formula filters the header using the values returned in point 1 above as criteria.
3. JOIN combines the point 2 output.
4. BYROW Lambda works in every row. So the row will be B2:D2 in the first row, B3:D3 in the second row, and so on.

This way, we can use BYROW to return the column header of max values in every row in Google Sheets.

Sample_Sheet_21521

Resources:

• Lookup and Retrieve the Column Header in Google Sheets.
• Find Max N Values in a Row and Return Headers in Google Sheets.
• Row-Wise MIN Using DMIN in Google Sheets.