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

#### TruffleOil

##### New Member
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
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
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))),"")``

#### TruffleOil

##### New Member
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
You're welcome & thanks for the feedback.

Replies
3
Views
175
Replies
7
Views
114
Replies
0
Views
136
Replies
6
Views
59
Replies
2
Views
91

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.

### Which adblocker are you using?

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

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