CODEWITHSUNDEEP
sortinggoogle-sheetsfilteringarray-formulasgoogle-sheets-query
Shian Han
Shian Han

Reputation: 317

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

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.

Upvotes: 2

Views: 1879

Answers (2)

user11982798
user11982798

Reputation: 1908

You can modified or(,,,,) like follow:

=SORT(
       FILTER(B11:C100, 
                ((B11:B100 = I11)*1+(B11:B100 = I12)*1+(B11:B100 = I13)*1+(B11:B100 = I14)*1)>0
       )
       , 2, 0
     )

Upvotes: 2

player0
player0

Reputation: 1

try:

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

enter image description here

Upvotes: 3

Related Questions