Lookup formula
UNIQUE / FILTER in Excel and Google Sheets
List unique values, or return every row that meets a condition.
Syntax
=UNIQUE(range) =FILTER(range, condition)- range
- The data.
- condition
- A test per row, e.g. B2:B100="Open".
Example
| Row | A | B |
|---|---|---|
| 1 | Client | Status |
| 2 | A | Open |
| 3 | B | Closed |
| 4 | C | Open |
| 5 | ||
| 6 | Open clients |
B6 =FILTER(A2:A4,B2:B4="Open")
Result: A, C
Spills every client whose status is Open.
Ready-to-copy formulas
Unique list of categories
=UNIQUE(C2:C500)Sorted unique list
=SORT(UNIQUE(C2:C500))
Tips
- Works in Excel 365, Excel 2021 and Google Sheets. Leave room below for the results to spill.
Need the whole spreadsheet?
Start from a free template with every formula already in place. Opening a template uses no credits.