Dynamic array formulas
FILTER, SORT, UNIQUE and friends: one formula that returns a whole table. 6 answers, each checked by running it, with a live example and an exercise.
How to filter rows based on a condition with a formula in Excel
=FILTER(A2:C10,B2:B10=G1)Use =FILTER(table, condition). It returns every matching row and spills the result below the formula.
How to use FILTER with multiple criteria in Excel
=FILTER(A2:C10,(B2:B10=G1)*(C2:C10>=G2))Multiply the conditions for AND and add them for OR: =FILTER(table,(cond1)*(cond2)).
How to sort data with a formula in Excel
=SORT(A2:B7,2,-1)Use =SORT(range, sort_column, -1) for largest first, or 1 for smallest first. The sorted copy spills and updates automatically.
How to get a list of unique values in Excel
=SORT(UNIQUE(B2:B10))Use =UNIQUE(range) to list each value once, and wrap it in SORT for alphabetical order: =SORT(UNIQUE(range)).
How to get the top 3 rows by value in Excel
=TAKE(SORT(A2:B8,2,-1),3)Sort the table by the value column, largest first, and take the first rows: =TAKE(SORT(table,2,-1),3). In Google Sheets use QUERY with limit.
How to return multiple matching values in one cell in Excel
=TEXTJOIN(", ",TRUE,FILTER(B2:B8,A2:A8=E1))Filter the matches and join them: =TEXTJOIN(", ",TRUE,FILTER(results,lookup_range=value)).