Forum Discussion
filter a table based on a list [UPDATED]
I have a table of data. Each row/record has a unique identifier. I have a separate list of unique identifiers that is a subset of those in the full table. I want to see the data for only the recor...
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.
- That's what I'm looking for, beautiful! Now to see if all my coworkers have UTD versions of Office...
Attached are the filter in place solutions. The TAKE workbook is for 365 and the OFFSET for legacy. Looks like you have some good options from everyone.
- You could use the provided solution in conditional formatting and then filter by color. The helper column wouldn't be needed.
Also a good idea. Is there a way to allow a conditional statement like that dynamically adjust for a shorter or longer list?
A formula that relatively references each list item would solve that issue, but the best I can think of is a VLOOKUP, but that's just going to return one data value per record, when I want to get all of them.
