Forum Discussion

JC Reardon's avatar
JC Reardon
Brass Contributor
Dec 14, 2022

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...
excel
Formulas and Functions
HansVogelaar's avatar
HansVogelaar
MVP
Dec 14, 2022

JC Reardon 

Would it be OK to return the filtered records to another range?

JC Reardon's avatar
JC Reardon
Brass Contributor
Dec 14, 2022
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.
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Dec 14, 2022

    JC Reardon 

    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.

    • JC Reardon's avatar
      JC Reardon
      Brass Contributor
      Dec 14, 2022
      That's what I'm looking for, beautiful! Now to see if all my coworkers have UTD versions of Office...
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Dec 14, 2022
    You could use the provided solution in conditional formatting and then filter by color. The helper column wouldn't be needed.
    • JC Reardon's avatar
      JC Reardon
      Brass Contributor
      Dec 14, 2022

      Patrick2788 

      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.

Resources