## How to find common values from two lists

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

**Array formula in C2:**

Recommended article:

**Filter common values between two ranges using array formula in excel**

Comments(1) Filed in category: Compare, Excel

### How to create an array formula

- Select cell C2
- Click in formula bar

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

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.

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

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}

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

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.

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

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

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

Comments(0) Filed in category: Compare, Excel

**Filter common values between two ranges using array formula in excel**

Comments(1) Filed in category: Compare, Excel

**How to remove common values between two columns**

Comments(1) Filed in category: Compare, Excel

**Highlight common values in two lists using conditional formatting in excel**

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

### Category: Compare

Compare pricelists in excel 2007

This post describes how to compare two price lists in excel 2007. I am going to create three conditional formatting […]Comments(7) Filed in category: Compare, Excel

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

Comments(5) Filed in category: Compare, Excel, Unique distinct values

Compare two tables in excel: Remove common rows

Table of Contents Compare two tables: Remove common rows Compare two tables using a condition Let me demonstrate how to extract […]Comments(5) Filed in category: Compare, Records

Filter values that exists in all three lists

Peter Voss asks: This is close to what I need. I have three lists of email addresses. If an email […]Comments(5) Filed in category: Compare, Excel

What values are missing in List 1 that exists i List 2?

Question: How to filter out data from List 1 that is missing in list 2? Answer: This formula is useful […]Comments(4) Filed in category: Compare, Excel, Missing values

Filter common values from three columns in excel

Question: How do I filter values that exists in all three columns? Answer: Formula in A14: =INDEX(List1, MATCH(0, COUNTIF($A$13:A13, List1)+IF(IF(COUNTIF(List2, […]Comments(4) Filed in category: Compare, Excel

Compare and filter values between two lists in excel

Question: Hi, I have seen a lot of examples related to unique list ... mais not found what I need. […]Comments(4) Filed in category: Compare, Excel

Compare values between two columns and filter values existing in only one column

There are text values in column A and column B. The question is how do you compare the values in […]Comments(3) Filed in category: Compare, Excel

### 13 Responses to “How to find common values from two lists”

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

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

https://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:

https://intersect-lists.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('div-gpt-ad-1486744346002-0'); }); Hi have a look at How to find common values from two lists | Get Digital Help - Microsoft Excel resource that should do the […]