I want to create a spreadsheet which has an array of values split over 1 or more columns. I can do this successfully with the following formula:


This puts each of these values in 3 adjacent cells, which is exactly what I want.

However, next I want to be able to create a filter which can filter the rows based on the value being present in any of these cells. In the above example I can create a filter on the first column, but the filter options only show the value "Picard".

Here's a link to an example spreadsheet: https://docs.google.com/spreadsheets/d/1k9qo3fa0xpFIh31yTSn1odxJcNZ-uxegYRecxfCwPJ4/edit?usp=sharing

Is what I'm trying to do possible, or should I move to a more full featured database?

1 Answer 1


This is what is shown in the linked spreadsheet

Please note that each column includes a dropdown button. That is the way that Filter/Filter views work on Google Sheets.

If you want that the filter shows all the values in the array, you should change the shape of the array from 3 columns 1 row to 1 column 3 rows as follows


If you want to keep the original array shape, then you will have to use another method to filter your array, like using functions (i.e. QUERY, FILTER)

  • Thanks, that's handy but that doesn't work when you have multiple rows. I get the error "Array result was not expanded because it would overwrite data in B3.". Is there any way around that, or do you recommend I move to your function suggestion. (P.S. my example has been updated).
    – Ian Newson
    Feb 27, 2020 at 16:14
  • @IanNewson Insted of changing your example, you should create a new one otherwise it becomes unhelpful for this question, also you should create a new question for your follow-up question. Feb 27, 2020 at 16:27

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.