Build array with multiple filters

Allo,
I have a simple list of systems located in rooms. I have new data regarding certain rooms and i need to figure out which system is impacted.
If I had 4-5 rooms impacted I would use a filter, but I have decent amount of rooms impacted.

As a result, I would like a list of two columns:
- The first one being the system name (A, B, C...)
- The second one being the room into which it's located
which is prettymuch the raw data filtered by impacted rooms.

Any nice way to that?

Thanks

Try:


=LET(A,raw_data!A2:B5000,FILTER(A,ISNUMBER(MATCH(INDEX(A,,2),B4:B8,0))))

see file for a version in your locale.

Another way to get the list

Formula:

Please Login or Register  to view this content.

Or, for a shorter list

Formula:

Please Login or Register  to view this content.

You're welcome. Thanks for the rep.

Likewise...

This will sort both columns

Formula:

Please Login or Register  to view this content.