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:


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:


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


1 Answer 1


Please try the following:

=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

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. Feb 1, 2020 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, 2020 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... Feb 3, 2020 at 11:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.