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...
Helper columns were also considered, it just seems... kludgly. It's a pretty basic data query - find the records that match and display that data. There's got to be a way to return an undefined amount of data (columns) aside from manually choosing the values in the filter dropdown.
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...
- 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.
