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)))

Recommended article:

Filter common values between two ranges using array formula in excel

Question: How do I filter common values between two ranges using array formula in excel? Answer: Formula in B12: =INDEX(One, […]

Comments(1) Filed in category: Compare, Excel

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.

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

Comments(2) Filed in category: Count values, Excel

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.

COUNTIF function

Counts the number of times a value exists in a cell range.

Comments(5) Filed in category: Excel

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}

IF function explained

Check if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

Comments(9) Filed in category: Excel

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.

SMALL function and LARGE function

This function lets you extract any number in a cell range based on sort rank.

Comments(12) Filed in category: Excel

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.

INDEX function explained

Fetch a value in a data set based on coordinates.

Comments(14) Filed in category: Cross reference table, Excel, INDEX function

Download excel sample file for this tutorial

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

Recommended articles

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

This blog post describes how to extract common rows (records) from two lists in excel 2007. Sheet: List 1 Sheet: […]

Comments(7) Filed in category: Compare, Excel, Records

Filter values existing in range 1 but not in range 2 using array formula in excel

Question: How do I filter values existing in one range but not in an other? Answer: Formula in B13: =INDEX(One, […]

Comments(0) Filed in category: Compare, Excel

Filter common values between two ranges using array formula in excel

Question: How do I filter common values between two ranges using array formula in excel? Answer: Formula in B12: =INDEX(One, […]

Comments(1) Filed in category: Compare, Excel

How to remove common values between two columns

Question: How do i remove common values between two lists? Answer: The solution in this article, removes common values and […]

Comments(1) Filed in category: Compare, Excel

Highlight common values in two lists using conditional formatting in excel

Question: How do I highlight duplicates in two lists using conditional formatting? In other words, how do I highlight cell […]

Comments(0) Filed in category: Conditional formatting, Excel