Question: How can I compare two columns to find common values?


Array formula in C2:

=INDEX($A$2:$A$11, SMALL(IF(COUNTIF($B$2:$B$11, $A$2:$A$11), ROW($A$2:$A$11)-MIN(ROW($A$2:$A$11))+1, ""), ROW(A1))) + CTRL + SHIFT + ENTER.

How to create an array formula

  1. Select cell C2
  2. Click in formula bar
  3. Copy Paste array formula
  4. Press and hold Ctrl + Shift
  5. Press Enter

How to copy array formula

Copy cell C2 and paste it down as far as needed.


Download excel sample file for this tutorial.

(Excel 97-2003 Workbook *.xls)

How do i remove the cell errors in the sheet above:


How do i remove errors from a cell formula?

Recommended blog posts
Compare two lists of data: Filter common row records in excel
Functions in this article

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

SMALL(array, k)
Returns the k-th smallest number in this data set.

Returns the row number of a reference

MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

Counts the number of cells within a range that meet the given condition

This blog article is one out of five articles on the same subject.