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]

Sometimes it will be in the same cell as "month" but not always.

What does that mean ? Please give us some visibility on what is going to be in Column A .

 

[Darren_workforce]

What does that mean ? Please give us some visibility on what is going to be in Column A .

Sorry. Column A is primarily just dates but every so often will have a reference to a recurring appointment. I was hoping to extract just the items with the word "monthly" or "month" (varies depending on who is entering the data).

 

[RoryA]

You can't use wildcards with = and you need to add for an OR rather than multiply, so you'd need something like:

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

 

[Darren_workforce]

You can't use wildcards with = and you need to add for an OR rather than multiply, so you'd need something like:

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

OK thank you. Did not know wildcards do not work with the =. Also, it has to be an and, not an or. I need to locate both the cells with "month" as well as referencing the current month to restrict the search results. With the formula you provided, I got a #SPILL error. However, I changed the '+' to an '*' and it worked (sort of). It generated a list of all appointments for the whole year with "month". Something is off still as I just want the search results for current month.

Also, the formula in AO1

=MONTH(TODAY())

I'm not sure is working correctly. it's populating results of 1/4/1900. I attempted to put =TODAY() in AP1 and then just =MONTH(AP1) but it's doing the same thing. I thought MONTH was supposed to extract the month of the desired cell with a date in it.

 

[Alex Blakenburg]

If Month could be Monthly then the field is not validated and I have some concerns that there could be other combinations than
April Month & April Monthly.
Assuming that is not the case give this a try:

=FILTER('Master 2024'!$B:$B,ISNUMBER(SEARCH(TEXT(DATE(1,$AO$1,1),"mmmm")&" "&"Month",'Master 2024'!$A:$A)),"")

 

[RoryA]

Also, it has to be an and, not an or

No it does not. The same cell cannot be both a date and text containing the word month.

 

[Darren_workforce]

If Month could be Monthly then the field is not validated and I have some concerns that there could be other combinations than
April Month & April Monthly.
Assuming that is not the case give this a try:

=FILTER('Master 2024'!$B:$B,ISNUMBER(SEARCH(TEXT(DATE(1,$AO$1,1),"mmmm")&" "&"Month",'Master 2024'!$A:$A)),"")

I'm testing on data from 2023 since the year is finished. The formula appears to be pulling December data and not April.

 

[Darren_workforce]

No it does not. The same cell cannot be both a date and text containing the word month.

OK that makes sense, I thought the formula was trying to locate "April" and "month"/"monthly" so that's why I thought it required the AND. Thank you for the clarification on that.

 

[Alex Blakenburg]

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

I'm testing on data from 2023 since the year is finished. The formula appears to be pulling December data and not April.

There is nothing in "April Month" to indicate the year. How are you catering for the Year component ?
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 ?