Trying to filter a query using data validation drop down - numerical options work, but not a combination of numbers/letters

Question

Here is a copy of my sheet https://docs.google.com/spreadsheets/d/1b7ofD9f02yKiIvGYfCBi_GdhkRtgTAoY2NpftJmZaDQ/edit?usp=sharing

So my query is on the front tab 'Overall' in D1

=query(Data!A1:G,"SELECT * WHERE 1=1 "&IF(A2="All Raids",""," AND A contains "&A2&" ") &IF(B2="All Classes",""," AND LOWER(B) = LOWER('"&B2&"') "),1)

On the datasheet, I have people listen in raids 1,2 and 3.. and it works fine. However, if I were to change a value to something like BWL1 or BWL2, it will not display.

I got this code from a youtube tutorial, so I don't understand it 100%, but basically the "a2=all raids","" part is just to remove any filters, then the remaining is filtered by my drop-down menus, &A2& and &B2&.

Any ideas?

I've tried using A CONTAINS "&A2" instead of A = "&A2&", I just cant figure out a workaround to where any value (numberical+lettered) listed in my Datasheet column A works.

Thank you in advance for your time, I hope this makes sense :)

Answer 1

That's a known issue with query() function: query() has trouble with mixed datatypes (numeric and text in column A). To avoid this you can convert column A to text. See if this formula works

=query({ArrayFormula(to_text(Data!A1:A)), Data!B1:G},"SELECT * WHERE 1=1 "&IF(A2="All Raids","", " AND Col1 contains '"&A2&"'") &IF(B2="All Classes",""," AND LOWER(Col2) = LOWER('"&B2&"') "),1)

Note that, because of the use of 'contains' when '1' is selected in A2, it will also show rows containing BWL1. If you don't want that, change 'contains' to '='.