How To Enable Gaming Mode on Android 12

Back in Februry, folks at XDA had spotted an unreleased Gaming Dashboard in the works for Android 12. But up until now, there were no clear...

Friday

Column Header of Max Value in Google Sheets Using Array Formula

Posted by   on

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 complicating 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 maintained 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.


Column Header of Max Value in Google Sheets - Example

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 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 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.


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 the 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 number 1000. That means the last paragraph under the above subtitle is applicable here also.


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.


Formula to Combine Row Number with Max

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


The Range in Vlookup that Returns the Column Headers of Max Values

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


Conclusion


You may have additional questions like I have the same max quantity in two warehouses for an item. I want both the warehouse names. How do I get them?


If that is your requirement, I don’t have an array formula. You can find a non-array-based solution under the “Resources” below.


Sample_Sheet_21521


Resources:

No comments:
Write Comments

Hello Friends, welcome to autobloginc.blogspot.com we Hope You'll like it - COntact US
!!THANK YOU FOR YOUR SUPPORT!!