Lookup
Dropping repeats from a list
Definition
Returning the second distinct label from a list means wrapping UNIQUE in INDEX.
Syntax
=UNIQUE(array, by_col, exactly_once)
Arguments
| Argument | Meaning |
|---|---|
array |
The list of labels to scan. |
by_col |
FALSE compares the labels down the column. |
exactly_once |
TRUE keeps only labels that appear once and drops repeats. |
Worked example
A nursery list stores species in A2:A4. ivy appears twice; fern appears once.
| A: Species |
|---|
| ivy |
| ivy |
| fern |
Formula: =UNIQUE(A2:A4,FALSE,TRUE)
Result: fern. ivy appears twice, so only fern is kept.
Practice
Cells A2:A4 hold oak, oak, and pine. In B2, write a formula that returns the second distinct label from that list.
- In B2, write an INDEX and UNIQUE formula that returns the second unique value in A2:A4.
- B2 should show the second unique value in A2:A4.
Step 1 of 2
Enter =INDEX(UNIQUE(A2:A4),2) in B2.
The unique values of oak, oak, and pine are oak and pine, so the second is pine.
Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.
Loading the sheet…
B2 uses UNIQUE.
B2 now shows pine.
The next lesson is The smallest amount in one group.
Related functions
UNIQUE is the function that lists the distinct labels in the order they first appear.
Common mistake
Returning the first distinct label, as in =INDEX(UNIQUE(A2:A4),1): the distinct list starts with oak instead of pine.