I've got a list of customer feedback from a company's website. We'd like to filter the data by store, so we can shout out the staff members or stores that are doing particularly well, and give them extra credit. Obviously customers leaving feedback may mention the store location, the member of staff that they spoke to, or any other factor that they valued, so ideally I'd like the company to be able to add to a list of keywords that they can use for the filter.

The sheet that I've attached below is some test data that I've put together, in case the above isn't clear.

The closest I've got is using =FILTER(All!A1:A10,ISNUMBER("Cardiff",All!A1:A10),"No Results") - although I know this would have to be edited in detail every time another filter term needed to be added. Also, for some reason, I'm returning the error "FILTER has mismatched range sizes. Expected row count: 10, column count: 1. Actual row count: 1, column count: 1." when I try it.

Would really appreciate any help anyone can give me - I'm completely confused, and making no progress!


  • Consider showing your hand-entered expected results in the sample spreadsheet. Clearly identify where the expected results are. Oct 13, 2021 at 9:01

2 Answers 2


To find values in Master!A1:A that match the text string in cell C1 or the text string in cell C2, use this:

=iferror( filter( Master!A1:A, iferror(search(C1, Master!A1:A)) + iferror(search(C2, Master!A1:A)) ), "(no results)" )

  • That's perfect! Thanks so much. Annoying that the formula has to be updated each time I add another value (staff member arriving or similar) but definitely not the end of the world, and a lot better than the current method of doing it by hand!
    – Pod
    Oct 13, 2021 at 9:08


Allows specifying search values in multiple cells

     REGEXMATCH(A:A, "(?i)"&
     JOIN("|", QUERY(B:B, "WHERE B <> ''")))),
   "no match")

Sample Data

Formula is in C2

1 Reviews Lookup Values Formula
2 New York location is great! LOuiE (Formula Here) New York location is great!
3 I was rude but joE put up with me! Joe I was rude but joE put up with me!
4 New Jersey location is OK! York The rep Louie was very helpful
5 The rep Louie was very helpful

screenshot of table showing formula


  1. Column A is FILTERED for rows that include the text strings located in Column B.
  2. REGEXMATCH is used to perform the check and the regular expression is dynamically created as follows:
    1. (?i) is prepended to the expression to make it case insensitive.
    2. The expression is completed with a text string made up of substrings taken from an array returned by a QUERY which JOIN concatenates delimited by the regex | (or) operator.
  3. IFNA handles errors when no match is found.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.