FILTER using a wildcard AND current month in same column

[Darren_workforce]

Hello,

I am trying to populate data from (Master 2024 $B:$B) based on a FILTER formula requiring 2 criteria. However, both criteria will be located in Column A. First, the word "month" or monthly" will appear randomly throughout the column so I added a wildcard for that and then the current month will also appear in Column A. Sometimes it will be in the same cell as "month" but not always. I have a formula referencing the current month in AO1 thinking that would be enough to reference in the FILTER formula but I'm receiving a #CALC! error. Any help would be greatly appreciated.

AO1:

=MONTH(TODAY())

Attempted formula:

=FILTER('Master 2024'!$B:$B,('Master 2024'!A:A="*Month*")*(MONTH('Master 2024'!A:A=AO1)))

 

[Darren_workforce]

If you have Rory's version working for you then please confirm and I will bow out.

No end results just yet.

There is nothing in "April Month" to indicate the year. How are you catering for the Year component ?

I anticipated I would not need to take into account the year simply because this would be a recurring search next year and I wanted to avoid having to update the year every year. I thought I could reference the current month with a formula off screen and have the FILTER formula reference that so it is always looking for whatever the current month was.

I setup AO1 =MONTH(TODAY()) but it's not giving me the current month, it's generating 1/4/1900. I may be incorrect in thinking it should extract the month from today's date.

 

[RoryA]

It does but you should format the cell as a number, not a date. 4 (April's month number) is the literal value of 4th Jan 1900.

 

[Darren_workforce]

If you have Rory's version working for you then please confirm and I will bow out.

Nothing finalized yet.

There is nothing in "April Month" to indicate the year. How are you catering for the Year component ?

I had hoped to reference the current month in text and use that in the FILTER search along with the word "month"/"monthly". I also anticipated the =TODAY() would take care of the Year component if that cell was referenced.

The formula I have should only pick up the rows with the text December Monthly or December Month and only if you have a 12 in AO1, is that the case ?

AO1 has altered between =MONTH(TODAY()) and =TODAY() in the hopes that the month within AO1 would work in the FILTER formula.

 

[Darren_workforce]

It does but you should format the cell as a number, not a date. 4 (April's month number) is the literal value of 4th Jan 1900.

Oh that's probably what the issue was, thank you. Trying to use Alex's formula and it's generating all instances through the whole year.

Your formula is resulting in a #SPILL error but I'm not sure why. There's nothing anywhere around the cell to prevent the results from populating.

 

[RoryA]

I had a bracket in the wrong place:

=FILTER('Master 2024'!$B:$B,(ISNUMBER(SEARCH("Month",'Master 2024'!A:A)))+(MONTH('Master 2024'!A:A)=AO1))

 

[Darren_workforce]

I had a bracket in the wrong place:

=FILTER('Master 2024'!$B:$B,(ISNUMBER(SEARCH("Month",'Master 2024'!A:A)))+(MONTH('Master 2024'!A:A)=AO1))

Now it's a #VALUE error. I tried just changing AO1 to "April" and the #VALUE error is still there

 

[RoryA]

Change it to not use entire columns. You probably have a header row that will cause an error when MONTH is applied to it.

 

[Darren_workforce]

Change it to not use entire columns. You probably have a header row that will cause an error when MONTH is applied to it.

I wondered that myself. I've never seen issues in the past but I do have headers in the Master 2024 tab. I changed the Master tab references to avoid Row 1.

=FILTER('Master 2024'!$B2:$B999,(ISNUMBER(SEARCH("Month",'Master 2024'!A:A)))+(MONTH('Master 2024'!A2:A999)=AO1))

But I am still getting the #VALUE error.

If the word "month" or the current month 'name' appears ANYWHERE else within the Master tab in any column other than A, will that cause any possible concerns with the formula? I know it's restricted to searching A but the word may appear in Column B. Just trying to eliminate any possible issues.

I'm not sure.

 

[RoryA]

I'm being dense! MONTH obviously also won't work on any cells that don't have dates in them! Perhaps:

=FILTER('Master 2024'!$B2:$B999,IFERROR(MONTH('Master 2024'!A2:A999)=AO1,ISNUMBER(SEARCH("Month",'Master 2024'!A2:A999))))

 

[Darren_workforce]

I'm being dense! MONTH obviously also won't work on any cells that don't have dates in them! Perhaps:

=FILTER('Master 2024'!$B2:$B999,IFERROR(MONTH('Master 2024'!A2:A999)=AO1,ISNUMBER(SEARCH("Month",'Master 2024'!A2:A999))))

OK we're good!! Thank you for your diligent effort on this one!