Removing duplicates with Index Match

Index function simply returns the content of a cell, specified by row and column offset. You can locate a specific cell content by specifying the location of the cell.

Syntax: =INDEX(reference, row number, column number)

The Match function returns the relative position of an item in a range that matches a specified value.

Syntax: =MATCH (search_key, range, [search_type])

search_key - The value to search for.

range - The one-dimensional array to be searched.

search_type - [ OPTIONAL - 1 by default ] - The manner in which to search.

1, the default, causes MATCH to assume that the range is sorted in ascending order and return the largest value less than or equal to search_key.

0 indicates exact match, and is required in situations where range is not sorted.

Index Match returns the content of the cell specified by Match function. If the content is not present in the range you will see output #N/A.

=INDEX(reference, MATCH(search_key, range, search_type)).

reference - column that we want to check for duplicates. For example, D1:D20

search_key - the first cell in the column, for example, D4, later formula will be applied to the whole column

range - column before cell that contains our search key. In that case before cell D4

search_type - 0 (because we want an exact match)

$ - static indicator. When we will apply the formula all numbers that don’t have static indicator will be changed.