Forum Discussion
Using Index/Match to return a list of clients who are active between 2 dates
I have a list of all of the clients I see throughout the year. From that data, I am trying to return a list of clients who are active between 2 dates (any given month)
For context, I'm using 2 sheets. Sheet 1 is called "Cts" and includes data in A1:I34 with headings. The relevant columns are A: Client Names, G: Open Date, H: Close Date. Sheet 2 is called "M1" and includes data in A1:F1 headings with reference cells G2 and H2.
Beginning in cell A2 on sheet "M1", I want to return ALL of the clients from Cts!A:A who have an open date (Cts!G:G) equal to or less than H2 and who have a close date (Cts!H:H) greater than or equal to G2.
I also want to either hide null/empty/error responses OR sort the values so the empty cells are all together.
1 Reply
- OliverScheurichGold Contributor
=IFERROR(INDEX(Cts!$A$2:$A$10,SMALL(IF((Cts!$G$2:$G$10<='M1'!$H$2)*(Cts!$H$2:$H$10>='M1'!$G$2),ROW(Cts!$A$2:$A$10)-1),ROW(Cts!B1))),"")
You can try this formula in cell A2 of sheet "M1". Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021. Fill the formula down as required.
Sheet "Cts":
Sheet "M1":