Creating a dynamically sorted and filtered list from a range using a single formula in Google Sheets

Question

I'm trying to do something similar to this question: Google sheets using Filter and Sort together

I have an input range with two columns, and as output I want to create a dynamically sorted and filtered list from the input range, using a single formula.

See this document for the desired result: https://docs.google.com/spreadsheets/d/109xcbORFZxTjH0Vjd6PVqYlOxMIdK7aXqf5-jnMMPik/edit?usp=sharing

I tried the formula: =SORT(FILTER(B11:C100, B11:B100 = or(I11,I12,I13,I14)), 2, 0) but it doesn't work. What I am doing wrong here? Any help much appreciated.

Answer 1

try:

=ARRAYFORMULA(QUERY(B11:C, 
 "where lower(B) matches '"&TEXTJOIN("|", 1, LOWER(I11:I))&"' 
  order by C desc", 0))