Google sheets query searching multiple columns with space seperator within a cell

Question

Now this following query runs great on one column and does use a space as a search separator however it only seaches one column.

=QUERY(Data!A1:O, "SELECT * WHERE LOWER(N) LIKE LOWER(""%" &JOIN("%"") AND LOWER(N) LIKE LOWER(""%", SPLIT(B1," "))&"%"")",1)

However then I found this snippet and that searches the entire sheet but cannot separate words within a cell.

=ARRAY_CONSTRAIN(IFERROR(QUERY({Data!A:O, TRANSPOSE(QUERY(TRANSPOSE(Data!A:O),,99^99))}, "where lower(Col16) contains '"&LOWER(B1)&"'", 1)), 99^99, COLUMNS(A:O))

The issue is I want to search multiple of my columns namely D,E,G,H,M,N where M contain multiple words that should be searched separated by a comma and space since the data comes from a form. Is there a way that makes it possible to achieve this?

Link to a very obfuscated sheet upon request data is somewhat similar yet document is very simplified and shortened. https://docs.google.com/spreadsheets/d/1PxdObZsn62rQ3QeYVdy9HjToIHZsmw1d0cXK2jOuiC4/edit?usp=sharing

Answer 1

Solution

In this case you should use the same approach you were using before, but now when searching in the built Col16:

=ARRAY_CONSTRAIN(IFERROR(QUERY({Data!A1:O, TRANSPOSE(QUERY(TRANSPOSE(Data!A1:O),,99^99))}, "where lower(Col16) LIKE LOWER(""%" &JOIN("%"") AND LOWER(Col16) LIKE LOWER(""%", SPLIT(B1," "))&"%"")", 1)), 99^99, COLUMNS(A:O))