How To Find Matching Cells In Excel

You can watch a video tutorial here.

Finding matching cells or duplicates is an operation you will frequently need to do when working with datasets. This happens especially when you are combining data from multiple sources and need to identify duplicates or matches. In Excel, there are 2 ways of doing this:

VLOOKUP() function: this searches for a value in the left-most column of a range, and returns the value in the column specified from the row of the match in the range.
1. Syntax: VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
  1. lookup_value: the cell reference of the first value in the key column from Table 1 (Name)
  2. table_array: the range of Table 2 which is made constant with the addition of dollar signs ($)
  3. col_index_num: the number of the column in Table 2 that has to be retrieved (1 i.e. the Name)
  4. range_lookup: TRUE will return an approximate match, FALSE will return an exact match
Conditional formatting: this will highlight duplicate values in a range of data

In this example, we will use VLOOKUP() to find cells from the first column that match those in the second column.

Option 1 – Use VLOOKUP()

Step 1 – Start typing the formula

Select the cell where the result is to appear
Type the formula using cell references:

=VLOOKUP(Names in 2010, $range of ‘Names in 2015’$,1,FALSE)

The col_index_num is set to 1 because there is only one column
Make the range of ‘Names in 2015’ constant by selecting the cell reference and pressing F4
Press Enter

Step 2 – Enhance the formula to handle errors i.e. missing values

If the lookup function cannot find a match in the second column i.e. ‘Names in 2015’, an error message will be returned – #N/A
Change the formula to display an empty cell if a match cannot be found:

= IFERROR(<vlookup formula>,””)