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), MATCH(ROW($A$2:$A$11),ROW($A$2:$A$11)), ""), ROWS($A$1:A1)))

How to create an array formula

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

You can check using the formula bar that you did above steps right, excel tells you if a cell contains an array formula by surrounding the formula with beginning and ending curly brackets, like this: {=array_formula}.

Don't enter these characters yourself they show up automatically if you did above steps correctly.

How to copy array formula

Copy cell C2 and paste it to cells below as far as needed.

common-values_1.png

Explaining array formula in cell C2

Step 1 - Compare cell range 1 with cell range 2

The COUNTIF function lets you compare values if you enter it as an array formula and use multiple values as criteria. COUNTIF(range, criteria)

COUNTIF($B$2:$B$11, $A$2:$A$11)

becomes

COUNTIF({"GG"; "HH"; "II"; "JJ"; "KK"; "LL"; "MM"; "NN"; "OO"; "PP"}, {"AA"; "BB"; "CC"; "DD"; "EE"; "FF"; "GG"; "HH"; "II"; "JJ"})

and returns

{0; 0; 0; 0; 0; 0; 1; 1; 1; 1}

This tells us that AA exists 0 times in cell range B2:B11,
BB - 0, CC - 0, DD - 0, EE-0, FF - 0

but GG is found once in cell range B2:B11 and so are HH, II, JJ.

Step 2 - Check if value exists, if so return corresponding position in array

IF({0; 0; 0; 0; 0; 0; 1; 1; 1; 1}, MATCH(ROW($A$2:$A$11),ROW($A$2:$A$11)), "")

becomes

IF({0; 0; 0; 0; 0; 0; 1; 1; 1; 1}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10}, "")

and returns

{0; 0; 0; 0; 0; 0; 7; 8; 9; 10}

Step 3 - Extract k-th smallest value

Until now we have been working with an array of values but excel allows us to only display one value per cell (That is not entirely true, as of Excel 2016 you can display all values in an array in one cell)

To extract a specific number from an array I use the SMALL function.

SMALL(IF(COUNTIF($B$2:$B$11, $A$2:$A$11), MATCH(ROW($A$2:$A$11),ROW($A$2:$A$11)), ""), ROWS($A$1:A1))

becomes

SMALL({""; ""; ""; ""; ""; ""; 7; 8; 9; 10}, ROWS($A$1:A1))

becomes

SMALL({""; ""; ""; ""; ""; ""; 7; 8; 9; 10}, 1)

and returns number 7, SMALL function ignores blanks and letters.

Step 4 - Return corresponding value

INDEX($A$2:$A$11, SMALL(IF(COUNTIF($B$2:$B$11, $A$2:$A$11), MATCH(ROW($A$2:$A$11),ROW($A$2:$A$11)), ""), ROWS($A$1:A1)))

becomes

INDEX($A$2:$A$11, 7)

becomes

INDEX({"AA"; "BB"; "CC"; "DD"; "EE"; "FF"; "GG"; "HH"; "II"; "JJ"}, 7)

and returns GG in cell C2.

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

Download excel sample file for this tutorial.

common-values1.xlsx
(Excel 2007 Workbook *.xlsx and later versions)

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.