# Extract shared values between two columns

This article demonstrates ways to extract shared values in different cell ranges, two and three cell ranges. The Excel 365 formulas are smaller and easier to understand.

#### Table of Contents

## 1. Extract shared values between two columns

**Question:**How can I compare two columns to find values that exist in both cell ranges?

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 are in both lists, see the 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:

Recommended articles

The image above shows an array formula in cell B12 that extracts values shared by cell range B2:D4 (One) and […]

### 1.1 How to create an array formula

- Select cell F3
- Press with left mouse button on 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.

Recommended articles

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

**1.2 How to copy array formula**

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

### 1.3 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", press with left mouse button 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)

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, EE-0, FF - 0

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

Recommended articles

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

The array is shown in column H below.

Recommended articles

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

#### 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($D$3:$D$12, $B$3:$B$12), MATCH(ROW($B$3:$B$12),ROW($B$3:$B$12)), ""), ROWS($A$1:A1))

returns number 7, SMALL function ignores blanks and letters.

Recommended articles

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

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.

Recommended articles

Gets a value in a specific cell range based on a row and column number.

### Get excel sample file for this tutorial

common-values1.xlsx

(Excel 2007 Workbook *.xlsx and later versions)

## 2. Extract shared values between two columns - Excel 365

This Excel 365 dynamic array formula extracts values from cell range B3:B12 only if they also exist in cell range D3:D12.

Formula in cell F3:

### 2.1 Explaining formula

#### Step 1 - Find values in common

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)

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

#### Step 2 - Extract values

The FILTER function extracts values/rows based on a condition or criteria.

FILTER(*array*, *include*, [*if_empty*])

FILTER($B$3:$B$12,COUNTIF($D$3:$D$12, $B$3:$B$12))

becomes

FILTER($B$3:$B$12, {0; 0; 0; 0; 0; 0; 1; 1; 1; 1})

and returns

{"GG"; "HH"; "II"; "JJ"}

## 3. Extract shared values between two columns - case sensitive

This formula shown in the image above extracts values in the first cell range if they also exist in the second cell range, upper and lower letters are also evaluated.

Excel 365 dynamic array formula in cell F3:

### 3.1 Explaining formula

#### Step 1 - Rearrange values from vertical to horizontal

The TRANSPOSE function converts a vertical range to a horizontal range, or vice versa.

TRANSPOSE(*array*)

TRANSPOSE(D3:D12)

returns {"GG", "HH", "II", ... , "aa"}.

#### Step 2 - Compare values based on upper and lower letters

The EXACT function performs a case sensitive comparison between values.

EXACT(*value1*, *value2*)

EXACT(B3:B12,TRANSPOSE(D3:D12))

returns an array shown in the image below. I have added the corresponding values from both cell ranges and highlighted values that exist in both cell ranges.

#### Step 3 - Convert boolean values

The asterisk lets you multiply numbers in an Excel formula, it also lets you convert boolean values to their numerical equivalents.

TRUE -> 1

FALSE -> 0 (zero)

EXACT(B3:B12,TRANSPOSE(D3:D12))*1

becomes

{FALSE,FALSE,FALSE, ... ,FALSE}*1

and returns

#### Step 4 - Create an array containing 1's

The ROW function returns the corresponding row number in a cell reference or multiple row numbers if a cell range reference is used.

ROW(*reference*)

ROW(B3:B12)^0

becomes

{3; 4; 5; 6; 7; 8; 9; 10; 11; 12}^0

and returns

{1; 1; 1; 1; 1; 1; 1; 1; 1; 1}.

#### Step 5 - Sum numbers row-wise

The MMULT function calculates the matrix product of two arrays, an array as the same number of rows as array1 and columns as array2.

MMULT(array1, array2)

MMULT(EXACT(B3:B12,TRANSPOSE(D3:D12))*1,ROW(B3:B12)^0)

becomes

MMULT({0,0,0, ... ,0},{1; 1; 1; 1; 1; 1; 1; 1; 1; 1})

and returns

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

#### Step 6 - Filter values based on array

The FILTER function extracts values/rows based on a condition or criteria.

FILTER(*array*, *include*, [*if_empty*])

FILTER($B$3:$B$12,MMULT(EXACT(B3:B12,TRANSPOSE(D3:D12))*1,ROW(B3:B12)^0))

returns

{"HH"; "II"; "JJ"}.

## 4. Filter common values from three separate columns

The image above demonstrates a formula in cell B15 that extracts values if they exist in all three cell ranges B3:B12, D3:D12, and F3:F12.

Array formula in B15:

Copy cell B15 and paste it to cells below as far as necessary.

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

### Explaining formula in cell B15

#### Step 1 - Prevent duplicates in the list

The COUNTIF function counts values based on a condition or criteria. The first argument $B$14:B14 expands as the cell is copied to cells below. This makes the formula aware of displayed values above the current cell.

COUNTIF($B$14:B14, $B$3:$B$12)

returns {0;0;0... ;0}

#### Step 2 - Find position of value in array

The MATCH function returns a number representing the position of a value in a list.

MATCH(0,COUNTIF($B$14:B14,$B$3:$B$12)+(((COUNTIF($D$3:$D$11,$B$3:$B$12)>0)+(COUNTIF($F$3:$F$12,$B$3:$B$12)>0))<>2),0)

returns 3.

#### Step 3 - Return value

The INDEX function returns a value based on a row and column number.

INDEX($B$3:$B$12, MATCH(0, COUNTIF($B$14:B14, $B$3:$B$12)+IF(((COUNTIF($D$3:$D$11, $B$3:$B$12)>0)+(COUNTIF($F$3:$F$12, $B$3:$B$12)>0))=2, 0, 1), 0))

returns "CC" in cell B15.

### Get Excel *.xlsx file

Common values in three columns.xlsx

### Compare category

This article describes an array formula that compares values from two different columns in two worksheets twice and returns a […]

Table of Contents Compare tables: Filter records occurring only in one table Compare two lists and filter unique values where […]

This article demonstrates techniques to highlight differences and common values across lists. What's on this page How to highlight differences […]

### Excel categories

### 24 Responses to “Extract shared values between two columns”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

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.

Hello,

I am not certain why the range of $A$13:A13 is used in the countif function... Is this a mistake?

Also, how can I adjust this to check for common values in 15 columns? Is that even possible?

Thanks,

Beth

Beth,

I am not certain why the range of $A$13:A13 is used in the countif function... Is this a mistake?It makes sure that only unique distinct values are extracted.

See the explanation in this post:

Filter values that exists in all three lists

Also, how can I adjust this to check for common values in 15 columns? Is that even possible?Yes, add the remaining 12 columns to the formula, using a countif function for each column. See explanation.

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.Press with left mouse button on 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

Hi Oscar,

Is there a way to do what you did above but without the named ranges?

Thanks

Dan

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: […]

How can we find common numbers from different sheets and arrange them with column heading and by counting that how many time a found in which Sheet???

[…] { 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 […]

REGARDING THE ABOVE QUESTION "Also, how can I adjust this to check for common values in 15 columns? Is that even possible?

Yes, add the remaining 12 columns to the formula, using a countif function for each column. See explanation."

Can you tell me what to insert - for my 3 columns I have =INDEX(A2:A16, MATCH(0, COUNTIF($A$19:A19, A2:A16)+IF(IF(COUNTIF(B2:B16, A2:A16)>0, 1, 0)+IF(COUNTIF(C2:C16, A2:A16)>0, 1, 0)=2, 0, 1), 0)). How do I add further columns into the range? I have tried copy and pasting +IF(COUNTIF(C2:C16, A2:A16)>0, 1, 0)before the = and changing it to d2:d16 but get an error

Louise

Great question!

Array formula in cell B14:

To add a fifth column (Col E) simply add a COUNTIF function.

I tried below formula for the fourth and fifth columns but only one item is listed other NA

What did I do wrong??

Array formula in cell B14:

=INDEX($A$2:$A$11,MATCH(0, COUNTIF($B$13:B13, $A$2:$A$11)+NOT(COUNTIF($B$2:$B$10, $A$2:$A$11)*COUNTIF($C$2:$C$11, $A$2:$A$11)*COUNTIF($D$2:$D$9, $A$2:$A$11)), 0))

To add a fifth column (Col E) simply add a COUNTIF function.

=INDEX($A$2:$A$11,MATCH(0, COUNTIF($B$13:B13, $A$2:$A$11)+NOT(COUNTIF($B$2:$B$10, $A$2:$A$11)*COUNTIF($C$2:$C$11, $A$2:$A$11)*COUNTIF($D$2:$D$9, $A$2:$A$11)*COUNTIF($E$2:$E$9, $A$2:$A$11)), 0))

Albert,

Can you post your formula?

Hi Oscar! I liked your solution. It worked fine for me.

However I have a different challenge now: to make it work for 7 columns, where I may select only some of them to make this analysis. The way the formula is will only return me a value which is common for all the 7columns. Id like to have the chance to select 2 or 3 columns for instance (out of those 7) to apply the search. Of course the selected ones may change on each use. Any idea? Thank you much!

Thanks for such a good explanation.

Hi there! This is a great formula, how can I make sure it is case sensitive? Appreciate any info you can provide. Thanks

Jjoseph,

thank you! I have added a case sensitive formula to this article.