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

common-values.png

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.

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

common-values_1.png

Download excel sample file for this tutorial.
common-values.xls

(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

INDEX(array,row_num,[column_num])
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.

ROW(reference)
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

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

COUNTIF(range,criteria)
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.