0

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?

0
1

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

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

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.