Syntax

Extracting unique items from a list using 3 main functions are INDEX, MATCH and COUNTIF. In the example here, the formula in cell B2 is:

=IFERROR(INDEX(Data, MATCH(0, INDEX(COUNTIF(Unique, Data),0),0)),””)

Inside:

• Data is the name of array A$2: $ A$7, array of data belonging to Data Column.
• Unique is the name of array B$1: B1, which is a dynamic data array. This array aims to subtract existing data from the result array.

Explain the Syntax

The basic formula here is INDEX (Data, number of rows). That is, INDEX function will get the result in the data array through number of rows. The problem here is how to find number of rows in INDEX function.

To find the number of rows, we will use COUNTIF function (Data, Unique). Here, COUNTIF function will count how many times the value in Data array appears in the dynamic array. When the formula of cell B2 is copied to the lower cells, the value of the single array will be expanded accordingly.

The following is the result of COUNTIF function of cells from B2 to B7:

Next, we use INDEX function to bring COUNTIF function value to the array value. From COUNTIF function result we will see number of rows that will be equal to the first zero in the array. Therefore, we will use the MATCH function to get this number. So the problem- number of rows has been solved. This combined with INDEX function mentioned in the beginning, we will get the results to find.

* In order to not show error values, we use IFERROR function if there is an error then the cell will return white value.