Lookup & reference
UNIQUE function
Distinct rows or columns of an array. UNIQUE works in Sheets with the usual arguments and results, on cells and on live data from the tools you connect.
Syntax
=UNIQUE(array, [by_col], [exactly_once]) | Argument | Use |
|---|---|
array | required |
by_col | optional |
exactly_once | optional |
Example
Spill each customer once.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Customer | Region | Status | Amount | Date |
| 2 | #1001 | Acme Ltd | North | Paid | 1200 | 2026-01-14 |
| 3 | #1002 | Blue Fox | South | Open | 450 | 2026-01-20 |
| 4 | #1003 | Acme Ltd | North | Paid | 980 | 2026-02-03 |
| 5 | #1004 | Cedar & Co | East | Overdue | 2300 | 2026-02-11 |
| 6 | #1005 | Blue Fox | South | Paid | 610 | 2026-03-02 |
| 7 | #1006 | Delta BV | West | Open | 1750 | 2026-03-09 |
| 8 | Acme Ltd | |||||
| 9 | Blue Fox | |||||
| 10 | Cedar & Co | |||||
| 11 | Delta BV |
Result computed by the Sheets formula engine; it spills into the cells below.
On live data
In Sheets, UNIQUE sits next to live data from a connected tool: =data.orders.sum(total) totals a table, =list.orders(customer) spills a column's distinct values, and a linked table column such as table.orders[Total] is a range any function accepts.
Try it in a free calculator
More lookup & reference functions
FILTERRows or columns that meet a conditionINDEXValue at a row/column of a rangeMATCHPosition of a value in a rangeSORTSort the rows or columns of an arrayVLOOKUPLook up down the first columnXLOOKUPLook up a value, return the matching itemXMATCHPosition of an item in a row or columnADDRESSCell address as textAREASNumber of areas in a referenceCHOOSEPick the Nth valueCHOOSECOLSSelected columns of an arrayCHOOSEROWSSelected rows of an array
Spreadsheet formulas on live business data
Sheets evaluates 491 spreadsheet functions, on cells and on data read live from your tools.