Filter values that exists in all three columns

Author: Oscar Cronquist Article last updated on October 11, 2019

This article explains how to extract values that exist in three different columns, they must occur in each of the three columns in order to be extracted. The example demonstrated in this article uses email addresses but you can use any kind of value.

The image above shows three different columns all containing email addresses, here is the question that inspired me to do this article.

Peter Voss asks:
This is close to what I need. I have three lists of email addresses. If an email address appears in all three (not two out of three) lists then place it in the duplicate column. Also, I need all three named ranges to be dynamic.

Named ranges

Named ranges allow you to create dynamic cell ranges that automatically grows when new data is added, this way you won't need to adjust cell references in formulas each time new values are added.

There are three columns so we need three different named ranges are necessary, here are the steps to create a named range:

Go to tab "Formulas" on the ribbon.
Press with left mouse button on "Name Manager" button.
Press with left mouse button on "New..." button.
Name the named range based on the data below.
Use the first formula below.
Press with left mouse button on OK button.
Repeat above steps so in total three named ranges are created, they are Email_Address, Email_Address2, Email_Address3.

Named range: Email_Address

Formula:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))

The COUNTA function counts the number of cells in column A that are not empty. The INDEX function uses that number to create a cell reference to the last not empty cell.

The named range concatenates the first cell reference and the second cell reference and creates a single cell reference to a cell range. If a new value is added the named range expands automatically, and it shrinks if the last value is deleted.

The disadvantage with this formula is that it doesn't take blank cells into account. If the cell range is not contiguous meaning there are blanks between values than the named range returns a smaller cell reference than required.

This named range formula takes care of that problem:

=Sheet1!$A$2:INDEX($A:$A,MATCH("ZZZZZZZZZZZZZZZZ",$A:$A))

Named range: Email_address2

Formula:

=Sheet1!$C$2:INDEX(Sheet1!$C:$C,COUNTA(Sheet1!$C:$C))

Named range: Email_address3

Formula:

=Sheet1!$E$2:INDEX(Sheet1!$E:$E,COUNTA(Sheet1!$E:$E))

Sheet 2 - Duplicate column

Array formula in cell A2:

=IFERROR(INDEX(Email_Address, MATCH(1, COUNTIF(Email_address2, Email_Address)*COUNTIF(Email_address3, Email_Address)*NOT(COUNTIF($A$1:A1, Email_Address)), 0)), "-")

How to enter an array formula

Select cell A2
Press with left mouse button on in formula bar
Copy/Paste or type above array formula
Press and hold Ctrl + Shift
Press Enter
The formula is now surrounded by curly brackets {=array_formula}

How to copy array formula

Select cell A2
Copy (Ctrl + c)
Select cell range A3:A8
Paste (Ctrl + v)

Explaining array formula in cell A2, sheet2

Step 1 - Identify email adresses in Email address 2 column that exists in Email address 1 column

The COUNTIF function counts cells based on a condition or multiple conditions. COUNTIF(range, criteria) In this case the function contains multiple conditions and if the number in the array is larger than 0 (zero) then the value exists in the other column.

COUNTIF(Email_address2, Email_Address)

becomes

and returns

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

Step 2 - Identify email adresses in Email address 3 column that exists in Email address 1 column

This part compares the first column with the third column.

COUNTIF(Email_address3, Email_Address)

becomes

and returns

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

Step 3 - Extract only unique distinct email addresse from Email Address 1 column

The NOT function changes booolean value TRU to FALSE and vice versa.

The COUNTIF function makes sure that only one instance of each value is returned.

NOT(COUNTIF($A$1:A1, Email_Address))

becomes

COUNTIF("Emails", {"[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"})

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}

Step 4 - Multiply all arrays

This part of the formula applies AND logic between the arrays, this is done by multiplying the arrays.

COUNTIF(Email_address2, Email_Address)*COUNTIF(Email_address3, Email_Address)*NOT(COUNTIF($A$1:A1, Email_Address))

becomes

{1; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0} * {1; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0} * {TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}

and returns

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

Step 5 - Find relative position of the first email address existing in all three lists

The MATCH function returns the relative position of a specific value in a cell range or array.

MATCH(1, COUNTIF(Email_address2, Email_Address)*COUNTIF(Email_address3, Email_Address)*NOT(COUNTIF($A$1:A1, Email_Address)), 0)

becomes

MATCH(1, {1; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}, 0)

and returns 1.

Step 6 - Return a value of the cell at the intersection of a particular row and column

The INDEX function returns a value from a cell range or aray based on a row and column number.

INDEX(Email_Address, MATCH(1, COUNTIF(Email_address2, Email_Address)*COUNTIF(Email_address3, Email_Address)*NOT(COUNTIF($A$1:A1, Email_Address)), 0))

becomes

INDEX(Email_Address, 1)

becomes

INDEX({"[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"}, 1)

and returns [email protected] in cell A2.

Get the Excel file

Filter-values-that-exists-in-all-three-lists.xlsx

Extract shared values between two columns

Question: How can I compare two columns to find values that exists in both cell ranges? The picture above shows […]

Filter common values from three separate columns

Array formula in B15: =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)) Copy cell B15 and paste it to […]

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

Compare two lists and filter unique values where the sum in one column doesn’t match the other column

How would you figure out an unique list where the sum of in one column doesn't match the other column? […]

Compare two columns and return differences

The image above demonstrates an array formula in cell B11 that extracts values that only exist in List 1 (B3:B7) […]

Extract a list of duplicates from a column

The array formula in cell C2 extracts duplicate values from column A. Only one duplicate of each value is displayed […]

Extract a list of duplicates from three columns combined

The following regular formula extracts duplicate values from column B (List1), D (List2) and F (List3) combined, the result is […]

Find min and max unique and duplicate numerical values

Question: How do I get the largest and smallest unique and duplicate value? The image below shows you a list […]

Filter duplicates within same date, week or month

The image above demonstrates a formula in cell E3 that extracts duplicate items if they are on the same date. […]

Comments (5)

5 Responses to “Filter values that exists in all three columns”

Peter Voss says:

October 2, 2012 at 10:18 pm

Absolutely outstanding! I struggled with a solution for weeks. Thank you not only for the solution but the explanation of how it all works.

Reply
Vanessa says:

October 23, 2012 at 10:11 pm

I am not able to do this for some reason... Using Excel 2011 on a Mac.
"Formula refers to empty cells." error...

Reply
- Oscar says:
  
  October 24, 2012 at 9:12 am
  
  Vanessa,
  
  Try creating tables instead of dynamic named ranges.
shamsher says:

November 10, 2012 at 7:04 am

Try creating tables instead of dynamic named ranges.

Reply
shamsher says:

November 10, 2012 at 9:25 am

i love it

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