filter with fomula

[mark-gabb]

hey all

i deeply appologise if ive missed something simple in my searches and i should know this already....

i have a list of values that will continue to increase for the foreseeable future...

i would like to filter this list by month for reports on a second page


so basically i have information in columns a-s
with about 1000 rows already

they are organized by month in the following order

mmm-yy

on my second page i have drop down with the list of months to choose from

i would like to create a list of only that particular month to generate reports from...but am unable to get excel to return the list

i know Google sheets has its filter function but that's not really possible for what im doing now...

i got about this far

=INDEX(DATA!A$3:s$10000,MATCH($B$1,DATA!$C$3:DATA!$C$10000)) but it only returns the one column, and then only repeatedly if you drag it down

[mark-gabb]

MOb fall draft.xlsm

this is my workings so far

[FDibbins]

I can think of a few different ways to do this.

1. Apply filters to the 1st sheet, then filter on whichever moth you want - no 2nd sheet needed.
2. using index/match without ARRAY, (which may slow your system down if you have a large database) This would use a helper column, that you can hide, if needed.
in Data U3, copy this down...
=IF(C3=REPORTING!$B$1,DATA!U2+1,DATA!U2)
Then on Reporting, A3, copied down and across...
=IFERROR(INDEX(DATA!A:A,MATCH(ROW(A1),DATA!$U:$U,0)),"")

3. with an ARRAY formula and no helper, IN rEPORTING a3, COPIED DOWN AND ACROSS...
=IFERROR(INDEX(DATA!A$3:A$4058,SMALL(IF(DATA!$C$3:$C$4058=REPORTING!$B$1,ROW(DATA!$A$3:$A$4058)-2),ROWS($A$1:A1))),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[mark-gabb]

3 is perfect....

thank you verry much

[FDibbins]

Happy to help, thanks for the feedback