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)))

 

[Alex Blakenburg]

I am a bit confused as to your requirements ?
The accepted solution will pick up anything with Month or Monthly in it ie April Month and May Month and June Month etc.

If you want to limit to a month you would need to combine the 2 methods.
As previously mentioned it still does not account for Year in the Text lines or for variations in how its written ie Apr Month woudn't get picked up.

=FILTER('Master 2024'!$B2:$B999,IFERROR(MONTH('Master 2024'!A2:A999)=$AO$1,ISNUMBER(SEARCH(TEXT(DATE(1,$AO$1,1),"mmmm")&" "&"Month",'Master 2024'!A2:A999))))