Use a cell reference to determine a range

Question

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?

Answer 1

I figured it out. I needed to use the indirect function

=INDEX(INDIRECT(D3):$AR$1,MATCH(TRUE,INDIRECT(C3):AR3=0,0))