Google Sheets has abundant formula options for retrieving the column header of a min value in a row.

In this post, I am sharing with you two of the best formulas from among them. One is a non-array formula, and the other is, of course, an array formula.

Both of them work differently.

If there is more than one min value in a row, the non-array formula will return multiple column headers.

At the same time, the array formula will only return the first column header.

But the advantage of the array formula is it would return the min value header in every row.

Please note that here column header means field label in the topmost row of your table.

**Formula to Return the Column Header of Min Value in Google Sheets**

Assume five employees are working under you. You want to assess their daily sales and to find the day-wise underperformer.

Here is the sample data in hand.

You can find the employee names on the column header (header row) and their date-wise sales in corresponding columns.

Amy | Dave | Maxine | Malcolm | Edgar | |

10/6/21 Thu | 3 | 1 | 5 | 3 | 8 |

11/6/21 Fri | 6 | 2 | 5 | 3 | 4 |

12/6/21 Sat | |||||

13/6/21 Sun | |||||

14/6/21 Mon | 2 | 5 | 4 | 1 | 1 |

15/6/21 Tue | 10 | 0 | |||

16/6/21 Wed | 3 | 3 | 4 | 1 | 2 |

17/6/21 Thu | 1 | ||||

18/6/21 Fri | 6 | 4 |

We can use QUERY or SORTN like functions for this purpose. But I would prefer Filter here.

Assume the above table is in the range A1:F10. Then we can use the below Filter formula in cell H2. Drag it down until cell H10 to return the column header of min value in every row.

`=ifna( filter( $B$1:$F$1, B2:F2=minifs(B2:F2,B2:F2,">0"), B2:F2<>"" )`

)

Can you please explain this Google Sheets formula?

Yep! Here you go!

- The MINIFS return the min value of the sales quantity in B2:F2 excluding zero and blanks.
- The FILTER formula filters the min value headers (here the employee names) matching the MINIFS result.
- In a blank row or a row that only contains 0, the MINIFS will return 0. The second condition in FILTER, i.e.,
`B2:F2<>""`

, is to return #N/A in that case. - The IFNA removes the above error.
- When you drag the formula down, it applies to other rows.

See this example live (GIF).

If you are a Google Sheets enthusiast, you might want to know the ‘rule’ I have used to format the range B2:F10. Here it is.

`=and(len($H2),$B2:$F2=minifs($B2:$F2,$B2:$F2,">0"))`

You can use this rule in Format > Conditional formatting.

## Array Formula to Retrieve the **Column Header of Min Value**

I have an array formula to retrieve the column header of the min value in Google Sheets. I have already mentioned the same. The formula has a limitation.

What’s that?

I mean, in the above example, the formula returns the names “Malcolm” and “Edgar” in row # 6 as the min value in that row is 1, which repeats twice.

My following array formula would only return the first min header, i.e., “Malcolm.”

`=ArrayFormula( ifna( if(len(A2:A), vlookup( row(A2:A)&"~"& dmin( transpose(if(A2:F>0,A2:F,)), sequence(rows(A2:A),1), if(,,);if(,,) ), Query(split(flatten(row(A2:A)&"~"&if(B2:F>0,B2:F,)&"🐟"&B1:F1),"🐟"), "Select * where Col1 is not null and Col2 is not null") ,2,0 ), ) ) )`

If you prefer to use this array formula to retrieve the column header of min value in each row in Google Sheets, you may want to go through the formula explanation.

But please keep remembering the disadvantage of this formula.

**If there are multiple min values in a row, the array formula would only return the min header of the first occurrence from left to right.**

In row # 6, it only returns the header “Malcolm.” The name “Edgar” is missing!

**Formula Explanation**

I am going to elaborate on the formula for you.

**Generic Formula (Vlookup Based):** `vlookup(row_nos&min_each_row,unpivot_table,2,0)`

In the above formula, I have used open ranges A2:A, A2:F, and B2:F. For the explanation, here I am using closed ranges A2:A10, A2:F10, and B2:F10.

Vlookup Syntax: `VLOOKUP(search_key, range, index, [is_sorted])`

Yep! We have used VLOOKUP to retrieve the column header of the min value excluding 0 in each row. Here are the expressions/references used in the arguments.

SEARCH KEY: `row_nos&min_each_row`

RANGE: `unpivot_table`

INDEX: 2

IS SORTED: 0

I know I must explain the search key and range in Vlookpup. So that, you can understand how the Vlookup returns the column header of min value in each row in Google Sheets.

**SERARCH KEY**

The search key here is the combination of min and row numbers (`row_nos&min_each_row`

) in each row. Here is that portion of the formula.

`row(A2:A10)&"~"&`

dmin( transpose(if(A2:F10>0,A2:F10,)), sequence(rows(A2:A10),1), if(,,);if(,,)

)

It would return the following result, if you use it as an ArrayFormula.

I have used DMIN as per the logic detailed here – Row-Wise MIN Using DMIN in Google Sheets.

**RANGE**

Since we have the row numbers combined with the min values in every row, we require a similar range for vertical lookup.

We can then only retrieve the column header of the min values.

What can we do here? Let’s unpivot the range (dataset) in a specific way.

The range is actually the unpivot data in B1:F10 (unpivot_table). Additionally, the row numbers from A2:A10 combined with the values (sales quantities) in B2:F10.

Here is the relevant part from the formula (range closed). The FLATTEN function with SPLIT does the job.

`Query(split(flatten(row(A2:A10)&"~"&if(B2:F10>0,B2:F10,)&"🐟"&B1:F1),"🐟"), "Select * where Col1 is not null and Col2 is not null")`

It will work with the array formula as standalone in Sheets. Here is that output.

Related: A Simple Formula to Unpivot a Dataset in Google Sheets.

Here, we have used the Vlookup function to lookup the search keys in the first column of the range to return the names from the second column.

That’s all. Thanks for the stay. Enjoy!

Related: Column Header of Max Value in Google Sheets Using Array Formula.

## No comments:

Write Comments