Dissecting and organizing data doesn’t have to be challenging. You can quickly filter your data items with this method using a combination of formulas to determine which are unique items.

## Syntax

=LOOKUP(2, 1/(COUNTIF(expanding unique list, original list)=0), original list)

## Steps

- Begin by typing in
**=LOOKUP(** - Follow with
**2,**and**1/COUNTIF(** - Select or type in the range reference that is one cell above the list, and make sure that the argument entered is an absolute reference (i.e.
**$D$2**) - Type in a colon and the same reference as the relative reference (i.e.
**:D2**) - Select or type in the range reference that contains the original list (i.e.
**$B$3:$B$9**) - Type in
**)=0),** - Select or type in the range reference that contains the original list (i.e.
**$B$3:$B$9**) - Type
**)**and press**Enter**to complete the formula - Copy down the formula all the way down
**=LOOKUP(2,1/(COUNTIF($D$2:D5,$B$3:$B$9)=0),$B$3:$B$9)**

## How

With this detailed formula we aim to find a unique value, which is not listed in any previous cells (i.e. **$D$2:D5**), by searching for a greater value (**2**) in an array of 1 and 0 values (**1/(COUNTIF($D$2:D5,$B$3:$B$9)=0)**).

Let’s breakdown the formula,

*=LOOKUP(2,1/(COUNTIF($D$2:D4,$B$3:$B$9)=0),$B$3:$B$9)*

Step | Description | Return Value |

1 | COUNTIF returns an array that contains 1 for each existed value of $B$3:$B$9 in expanding $D$2:D4 and 0 for not existed | {0;1;0;0;1;1;1} |

2 | By using an equal sign, the 1/0 array is converted into a TRUE/FALSE array | {TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE} |

3 | The number 1 is divided by the array, creates an array of 1s and #DIV/0 errors | {1;#DIV/0!;1;1;#DIV/0!;#DIV/0!;#DIV/0!} |

4 |
The number 2 is used as a larger number and is searched in the 1/error array. The LOOKUP returns the value in original list by using last non-error matched values index. |
4^{th} item in original list is pea |