Use a filter on an array of values

Question

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:

={"Picard","Q","Data"}

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?

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

={"Picard";"Q";"Data"}

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)