# Dynamic array formulas in Excel and Google Sheets

> FILTER, SORT, UNIQUE and friends: one formula that returns a whole table. 6 answers, each checked by running it.

Source: https://spreadsheetpractice.com/formulas/topic/dynamic · Updated October 29, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on October 29, 2026

## Answers

- [How to filter rows based on a condition with a formula in Excel](https://spreadsheetpractice.com/formulas/filter-rows-based-on-criteria): Use =FILTER(table, condition). It returns every matching row and spills the result below the formula. `=FILTER(A2:C10,B2:B10=G1)`
- [How to use FILTER with multiple criteria in Excel](https://spreadsheetpractice.com/formulas/filter-with-multiple-criteria): Multiply the conditions for AND and add them for OR: =FILTER(table,(cond1)*(cond2)). `=FILTER(A2:C10,(B2:B10=G1)*(C2:C10>=G2))`
- [How to sort data with a formula in Excel](https://spreadsheetpractice.com/formulas/sort-data-with-a-formula): Use =SORT(range, sort_column, -1) for largest first, or 1 for smallest first. The sorted copy spills and updates automatically. `=SORT(A2:B7,2,-1)`
- [How to get a list of unique values in Excel](https://spreadsheetpractice.com/formulas/list-unique-values): Use =UNIQUE(range) to list each value once, and wrap it in SORT for alphabetical order: =SORT(UNIQUE(range)). `=SORT(UNIQUE(B2:B10))`
- [How to get the top 3 rows by value in Excel](https://spreadsheetpractice.com/formulas/top-n-rows): 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. `=TAKE(SORT(A2:B8,2,-1),3)`
- [How to return multiple matching values in one cell in Excel](https://spreadsheetpractice.com/formulas/return-multiple-matches-in-one-cell): Filter the matches and join them: =TEXTJOIN(", ",TRUE,FILTER(results,lookup_range=value)). `=TEXTJOIN(", ",TRUE,FILTER(B2:B8,A2:A8=E1))`

More topics: [Counting formulas](https://spreadsheetpractice.com/formulas/topic/count) · [Sum formulas](https://spreadsheetpractice.com/formulas/topic/sum) · [Lookup formulas](https://spreadsheetpractice.com/formulas/topic/lookup) · [IF and logic formulas](https://spreadsheetpractice.com/formulas/topic/logic) · [Text formulas](https://spreadsheetpractice.com/formulas/topic/text) · [Date formulas](https://spreadsheetpractice.com/formulas/topic/date) · [Duplicates and data cleanup formulas](https://spreadsheetpractice.com/formulas/topic/cleanup) · [Rounding and math formulas](https://spreadsheetpractice.com/formulas/topic/math) · [Percentage formulas](https://spreadsheetpractice.com/formulas/topic/percent)

Every answer: [Formula answers](https://spreadsheetpractice.com/formulas). Formulas used: `FILTER`, `SORT`, `UNIQUE`, `TAKE`, `QUERY`, `TEXTJOIN`

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.