Forum Discussion
filter a table based on a list [UPDATED]
I'd add a helper column with formulas:
The formula in AA2 is =ISNUMBER(MATCH(A2,$A$9:$A$11,0)) where A9:A11 is the list of unique identifiers.
You can then filter for TRUE:
An alternative could be Power Query. In the attached file you can add data in the large blue dynamic table. In the small blue dynamic table you can enter the names you want to filter. Then click in any cell of the green table and right-click with the mouse. Then select refresh.
The layout in the screenshot is for illustration. The small blue table and the green table can be dragged to columns AB and AD:BC for example. The large blue table can then be expanded to thousands of rows.
- Exactly what I had in mind, thanks. Will this work with older / non-365 versions of Office?
You are welcome. I have Excel 2013 and Power Query works with this version.
Would it be OK to return the filtered records to another range?
- A new range would be optimal, yes. Your solution with the helper rows is certainly much better than what we are doing now but it would be great if we could achieve the same result without having to mess with the original table.
If you have Microsoft 365 or Office 2021, the formula would be
=FILTER(A2:Z6,ISNUMBER(MATCH(A2:A6,A9:A11,0)))
You only need to enter this in one cell; it will spill to as many rows and columns as needed.
