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: 27

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,418
Office Version
  1. 365
Platform
  1. Windows
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))),"")
 
Solution

TruffleOil

New Member
Joined
Sep 9, 2022
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
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!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,418
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,186,083
Messages
5,955,727
Members
438,213
Latest member
ngvicu

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
Top