Compare two columns for same values [Excel Formula]
The picture above shows two lists, one in column B and one in column D. The array formula in cell F3 extracts values that both lists have.
Array formula in cell F3:
In this case GG, HH, II and JJ is in both lists, see picture below.
The formula above can only compare two columns, however, the lists don't have to be the same size.
If you need to compare two different multicolumn cell ranges, read the following 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, […]
How to create an array formula
 Select cell F3

Click in formula bar
 Copy and paste the array formula above to formula bar
 Press and hold Ctrl + Shift simulateously
 Press Enter
 Release all keys
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 a 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.
How to copy array formula
Copy cell F3 and paste it to cells below as far as needed.
Explaining array formula in cell C2
You can easily examine a formula (or array formula) that you don't understand, select the cell containing the formula. Go to tab "Formulas", click on "Evaluate Formula".
The "Evaluate" button above lets you see the next "calculation" step.
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($D$3:$D$12, $B$3:$B$12)
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}
The array is shown in column H below.
This tells us that AA exists 0 (zero) times in cell range D3:D12,
BB  0, CC  0, DD  0, EE0, FF  0
but GG is found once in cell range D3:D12 and so are HH, II, JJ.
Learn to use the COUNTIF function
Counts the number of cells that meet a specific condition.
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($B$3:$B$12),ROW($B$3:$B$12)), "")
becomes
IF({0; 0; 0; 0; 0; 0; 1; 1; 1; 1}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10}, "")
and returns
{""; ""; ""; ""; ""; ""; 7; 8; 9; 10}
The array is shown in column H below.
Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.
Step 3  Extract kth 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($D$3:$D$12, $B$3:$B$12), MATCH(ROW($B$3:$B$12),ROW($B$3:$B$12)), ""), 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.
The SMALL function lets you extract a number in a cell range based on how small it is compared to the other numbers in the group.
Step 4  Return corresponding value
INDEX($B$3:$B$12, SMALL(IF(COUNTIF($D$3:$D$12, $B$3:$B$12), MATCH(ROW($B$3:$B$12),ROW($B$3:$B$12)), ""), ROWS($A$1:A1)))
becomes
INDEX($B$3:$B$12, 7)
becomes
INDEX({"AA"; "BB"; "CC"; "DD"; "EE"; "FF"; "GG"; "HH"; "II"; "JJ"}, 7)
and returns GG in cell F3.
When you copy cell F3 and paste it to cell F4 the relative cell references changes. ROWS($A$1:A1) becomes ROWS($A$1:A2) and returns 2 in cell F4.
The second smallest value is then extracted from the array which is 8. The value in cell range B3:B12 in row 8 is HH. HH is returned the value returned to F4.
Gets a value in a specific cell range based on a row and column number.
Download excel sample file for this tutorial
commonvalues1.xlsx
(Excel 2007 Workbook *.xlsx and later versions)
13 Responses to “Compare two columns for same values [Excel Formula]”
Leave a Reply
How to add a formula to your comment:
<code>your formula</code>
How to add VBA code to your comment:
[vb 1="vbnet" language=","]
VBA code
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.
Contact Oscar
You can contact me through this webpage
If you need to expand your comparison you will need to highlight all the rows, in the column, where you want the common values stored. Paste the above equation into the formula box, may need to use F2 button, and change the values of 17 to however many rows you need. The "" portion of the equation will need retyped as "". Use CTRL + SHFT + ENTER to run comparison. This is due to the equation using an array. Good Luck
Thank you for your comment. I have edited the article.
hi oscar,
i have a small problem which is similar to the one you have illustrated above, with the only difference being that i have some duplicates within List1 and List2 themselves.
eg. GG is repeated twice in List1, HH is repeated thrice in List2.
when i use this formula, i get duplicate GG and HH in Column 3. is there a way to prevent them from appearing? i tried a mishmash of this formula and a couple of others that i learnt on your website (ones listed in Duplicate category), but to no avail.
please help me out; i will appreciate that very much.
much thanks and kind regards.
K. Yantri,
Use this array formula in cell C2:
hi oscar,
thank you for your kind help in this regard.
Hi,
I am trying to compare the values in the excel where i have got list of values in the Column A and List of Values in Column B.
I want to find all the duplicate values in Row 1 and Row 2 but when i am applying above formulas getting #Value! error.
Anoop,
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
Hi Oscar,
Thanks for your quick reply.
PSB the proper example of my prob:
Question 1 How can we identify duplicate values from each column?
Question 2 If it is not possible to compare 4 column can we do it for 2?
Data 1 Data 2 Data 3 Data 4
1 5 9 11
2 6 10 12
3 4 4 4
4 7 7 7
5 8 8 8
Multinested formulas, as the one above, can be tough to decipher
The free/unlocked function syntax and usage navigation Addin (link below) can speedup your modelling work, if you are:
1) looking for multilingual documentation or translation for a function
2) searching for a new function to simplify your formulas
3) wondering, if a function is backwards compatible with previous Excel versions
https://www.spreadsheet1.com/syntaxandusagenavigationaddinforexcel2013functions.html
Very helpful, thanks!
BTW one can also use this simple online tool to ensure that the formula is correct:
https://intersectlists.hostei.com/
Hi Oscar,
Need a formula to count identical numbers in two columns but items must be in same row (position).
12 15
8 8 good count 1
22 19
7 22 for 22 not count cause is not in same row
14 14 good count 2
....
....
Thanks.
[…] Kidd asks: […]
[…] { googletag.display('divgptad14867443460020'); }); Hi have a look at How to find common values from two lists  Get Digital Help  Microsoft Excel resource that should do the […]