I have a list of Japanese words. I want to produce a formula to filter that list, based on a filter list of substrings (kanji characters) which I'm looking for.
So for example, say my filter list contains three characters, 水 and 木 and 日. I want to find every word in my list which contains 水 or 木 or 日. That's an inclusive or - any words which contain more than one of the characters on the filter list (such as 木曜日 or 水曜日) should show up, too.
So far, my formula works with just one character to filter for:
=QUERY(Sheet1!$A$1:$A$44492, "SELECT A WHERE A LIKE '%"&A2&"%'")
However, I've tried expanding it to a range, and it doesn't work:
=QUERY(Sheet1!$A$1:$A$44492, "SELECT A WHERE A LIKE '%"&A2:A4&"%'")
It only filters words containing the first character, 水.
Is there a way to filter the whole list based on all the items in my list? And to be clear, I want it to be dynamic, so that I can put in as many filter characters as I like to the list, rather than basing it on an exact number of filter characters.