I have a column with random date values in random order. I want to extract the indices of any date match which fall between a date range (Column B and C) AND a category match (Column E).
I cannot use any solution which will alter the indices, the Date and Category values should be at its index always. Dates are in the format MM/DD/YYYY.
Data Example and Expected Result in Column F:
| A | B | C | D | E | F |
|---|---|---|---|---|---|
| Dates | Start Range | End Range | Category | Category Condition | Indices |
| 11/02/2021 | 11/02/2021 | 11/25/2021 | A | A | 1 |
| 10/22/2021 | B | 3 | |||
| 11/22/2021 | A | 5 | |||
| 09/02/2021 | C | 6 | |||
| 11/15/2021 | A | #NA | |||
| 11/02/2021 | A | #NA |
Solution Tried: Using Match and Index formulas, I extracted the indices of Column D in a new column. Using Match and Index formulas, I extracted the indices of Column A in another column. Using the same formulas I found out the common indices. This increased the calculation time by a lot and Excel was not responded time to time.
How can I achieve the expected column F with minimal calculation time? I cannot upgrade Excel to Office 365, hence unable to use Filter formula.
CodePudding user response：
Use AGGREGATE:
=AGGREGATE(15,7,(ROW($A$2:$A$7)-MIN(ROW($A$2:$A$7)) 1)/(($A$2:$A$7>=$B$2)*($A$2:$A$7<=$C$2)*($D$2:$D$7=$E$2)),ROW($ZZ1))
