Apple Numbers: Use filter to find any cells containing values from column

[rbsam176]

I've mostly worked with Excel before but I'm having to achieve what I'm trying to do with Apple's Numbers application.

I have a spreadsheet full of values in column A on a table called Tracker.

I would like a separate table, Filter Criteria, which contains 1 column where I can paste a bunch of values in multiple cells and then I can activate a filter which will show me all of the cells in the Tracker table that are listed in Filter criteria table.

I would love to be able to do this with the built in Filter feature located in the sidebar on the right of the Numbers UI, however it seems to only let you type in text rather than referencing a cells or column of cells. Because my filter criteria could be quite long, it would take a long time to set up individual filters for each criteria value.

It would be great if someone could point me in the direction on how to achieve this.

Just to be clear, the 'expected filtered result' shouldn't be a new table, it should be the first table but simply filtered to only show the matching results.

 

[Stephen.R]

I'm sure there are other ways to do this, but this is how I tend to solve stuff like this in Numbers:

1. Add a column to the table you want to filter. I headed mine as "Match" but you can call it anything, it'll be hidden later.
2. In the first row in the new column, hit the equals key to get the formula popup, then enter this:

   IFERROR(VLOOKUP($A2,Filter criteria::A,1,FALSE),0)

3. Fill that formula down the table (either drag the little yellow dot, or select all the non-header rows in that column and go (menu bar) Table > Auto Fill Cells > Fill Down.
4. Right click the new column in the column letter (i.e mine was "C") and choose "Hide Column"
5. In the "Filter" sidebar, add a new rule, against the column you added, "text is not" and enter a zero in the box.
6. Enable the filter.
7. Profit.