## How to find common values from two lists

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

**Array formula in C2:**

**How to create an array formula**

- Select cell C2
- Click in formula bar

- Copy Paste array formula
- Press and hold Ctrl + Shift
- 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. **

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.*

**Filter values existing in range 1 but not in range 2 using array formula in excel****Filter common values between two ranges using array formula in excel****How to remove common values between two columns****How to find common values from two lists****Highlight common values in two lists using conditional formatting in excel**

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

Multi-nested formulas, as the one above, can be tough to decipher

The free/unlocked function syntax and usage navigation Add-in (link below) can speed-up your modelling work, if you are:

1) looking for multi-lingual 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

http://www.spreadsheet1.com/syntax-and-usage-navigation-add-in-for-excel-2013-functions.html

Very helpful, thanks!

BTW one can also use this simple online tool to ensure that the formula is correct:

http://intersect-lists.hostei.com/