How do I change my multi criteria Index Match formula in such a way that it sorts results closest to today?

Question

How can do I write an array formula in such a way that both following factors apply:

1. Results give me the names of sales that have either TRUE OR FALSE next to it in a different column/sheet.
2. Results are sorted chronologically based on the date that is connected to each sale. Each sale has a different date next to it. This can be found in the same sheet as where the "TRUE OR FALSE" result is displayed. Column with the dates is called "AY:AY". I use an indirect formula to target the correct sheet within the spreadsheet.

I currently only have the first criteria implemented, don't know how to do the 2nd one.

Since the raw data is not ordered I need this to happen when I use the Index Match Array formula. So far I have this formula in my Google Sheets spreadsheet.

=ArrayFormula(iferror(index(indirect("'"&$B$5&" 2023'!c:c");small(if(TRUE=indirect("'"&$B$5&" 2023'!ca:ca");row(indirect("'"&$B$5&" 2023'!ca:ca"))-row(indirect("'"&$B$5&" 2023'!$ca$1"))+1);row(1:1)));""))

I know I could use the Index Array formula below with multiple criteria, but don't know how to implement the date component.

INDEX(indirect("'"&$B$5&" 2023'!c:c");SMALL(IF(COUNTIF(_______)*COUNTIF("true"; indirect("'"&$B$5&" 2023'!CA:CA"); ROW(indirect("'"&$B$5&" 2023'!A:CA"))-MIN(ROW(indirect("'"&$B$5&" 2023'!A:CA"))+1); ROW(indirect("'"&$B$5&" 2023'!A1));COLUMN(indirect("'"&$B$5&" 2023'!A1))

Thanks in advance.

Answer 1

A query like this could help?

=QUERY(INDIRECT("'"&$B$5&" 2023'!C:CA"),"SELECT C,AY WHERE CA = TRUE order by AY")