Alternative for Filter function in Excel 2016 - Index Match for date ranges

TruffleOil

New Member
Joined
Sep 9, 2022
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
Hi everyone

I have a set of data with different notification dates for several projects (some different projects have the same notification date).
My goal: filter and list all the projects that fall within a certain date range, in this case by month and year. In this example, I want to show the list of all the projects with notification date in November(more or equal to 1stNov and less or equal 30Nov) (even if they have the same date), whenever I change the calendar month or year to that date. If I change it to April 2023, it should show Project C or more if available.

I saw a video that showed the Filter formula which works fine for this, but I have Excel 2016 and it's not available. I tried Index Match but I'm still a newbie and struggling to come up with a formula. Would appreciate if anyone could help with the right formula for this!!
 

Attachments

  • excel question.PNG
    excel question.PNG
    34.5 KB · Views: 55

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Hi & welcome to MrExcel.
How about in J8 dragged down
Excel Formula:
=IFERROR(INDEX($G$17:$G$100,AGGREGATE(15,6,(ROW($G$17:$G$100)-ROW($G$17)+1)/($G$17:$G$100>=$J$1)/($G$17:$G$100<=$L$1),ROWS(J$8:J8))),"")
and in L8
Excel Formula:
=IFERROR(INDEX($A$17:$A$100,AGGREGATE(15,6,(ROW($G$17:$G$100)-ROW($G$17)+1)/($G$17:$G$100>=$J$1)/($G$17:$G$100<=$L$1),ROWS(L$8:L8))),"")
 
Upvote 0
Solution
Hi & welcome to MrExcel.
How about in J8 dragged down
Excel Formula:
=IFERROR(INDEX($G$17:$G$100,AGGREGATE(15,6,(ROW($G$17:$G$100)-ROW($G$17)+1)/($G$17:$G$100>=$J$1)/($G$17:$G$100<=$L$1),ROWS(J$8:J8))),"")
and in L8
Excel Formula:
=IFERROR(INDEX($A$17:$A$100,AGGREGATE(15,6,(ROW($G$17:$G$100)-ROW($G$17)+1)/($G$17:$G$100>=$J$1)/($G$17:$G$100<=$L$1),ROWS(L$8:L8))),"")
wow can't believe it worked!! thank you so much Fluff, cheers!!
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,305
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top