# Simplest way to return header of max value in a row (for a dynamic range)?

I have a sheet with a table, where I want to extract the header of the max value in each row. The simple solution for that is:

``````=IFERROR(IF(AND(B2<>"Sa",B2<>"Su"),INDEX(\$C\$1:\$Y\$1,MATCH(MAX(C2:Y2),C2:Y2,0)),"weekend"),"")
``````

Problem is, that if you insert a column to the right on column Y, the formula will not change the range (it will still be C2:Y2, and data on column Z will not be included). For that I created this formula, which solves this issue:

`=iferror(if(AND(B2<>"la";B2<>"su");INDEX(\$C\$1:\$1;MATCH(MAX(INDIRECT("C"&row()&":"&SUBSTITUTE(ADDRESS(1;MATCH(OFFSET(INDIRECT(ADDRESS(1;COLUMN()));0;-5);\$1:\$1;0)-1;4);1;"")&row()));INDIRECT("C"&row()&":"&SUBSTITUTE(ADDRESS(1;MATCH(OFFSET(INDIRECT(ADDRESS(1;COLUMN()));0;-5);\$1:\$1;0)-1;4);1;"")&row());0));"w");"")`

I was wondering: is there an easier/simpler way to do this with google sheets formulas? Basically I want the range to be from `C1:<whatever letter is before the Total column>`.

Thanks!

``=IFERROR(IF(AND(B2<>"Sa",B2<>"Su"),INDEX(\$C\$1:\$1,MATCH(MAX(INDIRECT("C"&ROW()&":"&SUBSTITUTE(ADDRESS(1,MATCH(INDIRECT(ADDRESS(1,iferror(match("Total",\$A\$1:\$1,0)-1,"No match"))),\$1:\$1,0)-1,4),1,"")&ROW())),INDIRECT("C"&ROW()&":"&SUBSTITUTE(ADDRESS(1,MATCH(INDIRECT(ADDRESS(1,iferror(match("Total",\$A\$1:\$1,0)-1,"No match"))),\$1:\$1,0)-1,4),1,"")&ROW()),0)),"weekend"),"")``

What we do is replace the

``OFFSET(INDIRECT(ADDRESS(1,COLUMN())),0,-5)``
part of the formula with
``INDIRECT(ADDRESS(1,iferror(match("Total",\$A\$1:\$1,0)-1,"No match")))``

`INDIRECT(ADDRESS(1,iferror(match("Total",\$A\$1:\$1,0),"No match")))` will always return the column `Total` no matter where placed.
By adding `-1` it will return the column before `Total`, thus eliminating the need for the the `OFFSET` function.

• Has the provided formula work for you? If not let us know why. If it did, please remember that as per site guidelines when an answer addresses your question, you should accept it and even upvote it so others can benefit as well. – marikamitsos Feb 1 at 22:06
• Your formula works, but it doesn't address the question, which is to provide a simpler/shorter solution than the one provided above (yours is longer). – jlo Feb 3 at 10:19
• "Basically I want the range to be from C1:<whatever letter is before the Total column>" From what I understand and remember when looking at your sheet, you hadn't solved that issue. Anyway... – marikamitsos Feb 3 at 11:00