0

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!

0

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

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.

| improve this answer | |
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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