Use a cell reference to determine a range
I'm trying to find the beginning and end date of a list of clients based on whether or not revenue > 0. Beginning date was this.
=INDEX($E$1:$AQ$1,MATCH(TRUE,E2:AQ2>0,0))
For the end date, I want to start the cell reference at the cell pulled with the below function
=CELL("address",INDEX(E2:AQ2,MATCH(B2,$E$1:$AQ$1,0)))
So, combined, it would look like this
=INDEX($E$1:$AQ$1,MATCH(TRUE,CELL("address",INDEX(E2:AQ2,MATCH(B2,$E$1:$AQ$1,0))):AQ3=0,0))
But that returns an error. Thanks in advance for help
EDIT: the issue seems to be you can't use the =CELL() function in a range. Anyone have a solution to this?
Solution 1:
I figured it out. I needed to use the indirect function
=INDEX(INDIRECT(D3):$AR$1,MATCH(TRUE,INDIRECT(C3):AR3=0,0))