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.

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:

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

Named range: Email_Address

Formula:

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.

Named range: Email_address2

Formula:

Named range: Email_address3

Formula:

Sheet 2 - Duplicate column

Array formula in cell A2:

How to enter an array formula

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

How to copy array formula

1. Select cell A2
2. Copy (Ctrl + c)
3. Select cell range A3:A8
4. 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

COUNTIF({"[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]"; "[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

{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

COUNTIF({"[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]"; "[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

{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