I have the below formula which picks up the column header of the most recent date that is in a cell between N2 to Q2 and X2 to AA2 (ie not in o2 toW2). This works fine until a date in o2 is the same as N2 (providing there is no further dates in the later cells in the formula. If this happens, it returns the COlumn Q header. If the date in Column Q is afterwards it omits (correctly) from the return result.
(I am entering this as an array formula)
---N---|---P---|---Q---| …………|---X---|
AAAAA|BBBBBB|CCCCC|...…….|DDDDD|
1/5/19|1/5/19 ..........................................…… Returns BBBBBB (Correct)
1/5/19|1/5/19|1/5/19| ........................…………. Returns CCCCCC (Incorrect as Column Q should not be picked up)
1/5/19|2/5/19|3/5/19| .................................… Returns BBBBBBB (Correct - as Column Q is not in formula)
1/5/19|2/5/19|2/5/19|...………|2/5/19| ……………... Returns DDDDDDD (Correct, omits Col Q, X is in formula)
(I am entering this as an array formula)
---N---|---P---|---Q---| …………|---X---|
AAAAA|BBBBBB|CCCCC|...…….|DDDDD|
1/5/19|1/5/19 ..........................................…… Returns BBBBBB (Correct)
1/5/19|1/5/19|1/5/19| ........................…………. Returns CCCCCC (Incorrect as Column Q should not be picked up)
1/5/19|2/5/19|3/5/19| .................................… Returns BBBBBBB (Correct - as Column Q is not in formula)
1/5/19|2/5/19|2/5/19|...………|2/5/19| ……………... Returns DDDDDDD (Correct, omits Col Q, X is in formula)
Code:
=IFERROR(INDEX($N$1:$AA$1,LARGE(IF(MAX(N2:Q2,X2:AA2)=N2:AA2,COLUMN(N2:AA2)-COLUMN(N2)+1),1)),"")
Last edited:
