TransWikia.com

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

Web Applications Asked by jlo on December 18, 2020

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!

One Answer

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.

Answered by marikamitsos on December 18, 2020

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP