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.

Answer:

Sheet 1 - Three lists of email addresses

Named ranges

Named range: Email_Address

Formula:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$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

  1. Select cell A2
  2. Click 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

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

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

COUNTIF(Email_address2, Email_Address)

becomes

COUNTIF({"pcrzjzs@hotmail.com"; "rmwoolf@hotmail.com"; "veflq@earthlink.net"; "zvxfki@comcast.net"; "kluwyomabx@verizon.net"; "sxadcim@btinternet.com"; "bhjmbvncib@gmail.com"; "kjueztmbw@shaw.ca"; "kacqfxtuq@sbcglobal.net"; "pufgpupfo@yahoo.ca"; "gcnlgj@hotmail.com"; "pdobq@aol.com"; "frwccih@comcast.net"; "jmxrzab@btinternet.com"; "msavbp@cox.net"}, {"msavbp@cox.net"; "hhulyjp@msn.com"; "qjfnhlc@yahoo.ca"; "gfiek@gmail.com"; "ojybngdhv@rediffmail.com"; "xeekbg@verizon.net"; "frwccih@comcast.net"; "xjshwrpukj@yahoo.ca"; "vkyocsztrr@comcast.net"; "paekvqnsu@aol.com"; "xgwqojk@cox.net"; "zxjfgsilm@yahoo.ca"; "pektlh@sbcglobal.net"; "gabni@yahoo.co.uk"; "yspag@shaw.ca"; "ilevtwvkwp@charter.net"; "ofajlrrt@btinternet.com"; "jzhnleqz@shaw.ca"; "pcrzjzs@hotmail.com"; "cxxbaytyii@aol.com"})

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

COUNTIF(Email_address3, Email_Address)

becomes

COUNTIF({"atmboly@hotmail.co.uk"; "msavbp@cox.net"; "frwccih@comcast.net"; "rwninyra@ntlworld.com"; "onitbx@shaw.ca"; "eywkwp@charter.net"; "cwrtdofl@hotmail.co.uk"; "sjlndznav@hotmail.co.uk"; "gtbvxhg@hotmail.com"; "zpzoelnwvb@aol.com"; "zumnyzy@sbcglobal.net"; "pnnco@earthlink.net"; "mzvwtyzjr@sbcglobal.net"; "okzdntak@aol.com"; "ozzsobs@charter.net"; "suxju@sbcglobal.net"; "aetph@btinternet.com"}, {"msavbp@cox.net"; "hhulyjp@msn.com"; "qjfnhlc@yahoo.ca"; "gfiek@gmail.com"; "ojybngdhv@rediffmail.com"; "xeekbg@verizon.net"; "frwccih@comcast.net"; "xjshwrpukj@yahoo.ca"; "vkyocsztrr@comcast.net"; "paekvqnsu@aol.com"; "xgwqojk@cox.net"; "zxjfgsilm@yahoo.ca"; "pektlh@sbcglobal.net"; "gabni@yahoo.co.uk"; "yspag@shaw.ca"; "ilevtwvkwp@charter.net"; "ofajlrrt@btinternet.com"; "jzhnleqz@shaw.ca"; "pcrzjzs@hotmail.com"; "cxxbaytyii@aol.com"})

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

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

becomes

COUNTIF("Emails", {"msavbp@cox.net"; "hhulyjp@msn.com"; "qjfnhlc@yahoo.ca"; "gfiek@gmail.com"; "ojybngdhv@rediffmail.com"; "xeekbg@verizon.net"; "frwccih@comcast.net"; "xjshwrpukj@yahoo.ca"; "vkyocsztrr@comcast.net"; "paekvqnsu@aol.com"; "xgwqojk@cox.net"; "zxjfgsilm@yahoo.ca"; "pektlh@sbcglobal.net"; "gabni@yahoo.co.uk"; "yspag@shaw.ca"; "ilevtwvkwp@charter.net"; "ofajlrrt@btinternet.com"; "jzhnleqz@shaw.ca"; "pcrzjzs@hotmail.com"; "cxxbaytyii@aol.com"})

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

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

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

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({"msavbp@cox.net"; "hhulyjp@msn.com"; "qjfnhlc@yahoo.ca"; "gfiek@gmail.com"; "ojybngdhv@rediffmail.com"; "xeekbg@verizon.net"; "frwccih@comcast.net"; "xjshwrpukj@yahoo.ca"; "vkyocsztrr@comcast.net"; "paekvqnsu@aol.com"; "xgwqojk@cox.net"; "zxjfgsilm@yahoo.ca"; "pektlh@sbcglobal.net"; "gabni@yahoo.co.uk"; "yspag@shaw.ca"; "ilevtwvkwp@charter.net"; "ofajlrrt@btinternet.com"; "jzhnleqz@shaw.ca"; "pcrzjzs@hotmail.com"; "cxxbaytyii@aol.com"}, 1)

and returns msavbp@cox.net in cell A2.

Download excel *.xlsx

Filter values that exists in all three lists.xlsx