vlookup: match larger string against smaller (sub)string

[netarc]

I've figured out one can use wild cards ("*" & A1 & "*) with vlookup to match against a substring of the target array ... but i've got a situation where I've got to go the other way.

Specifically, sheet1 (target array) has a list of email addresses, one in each cell of the column; sheet 2 also has an email address column, but the cell could contain one, two or even three email addresses, seperated by commas.

I need to find whether any one of the email addresses in a given cell of sheet 2 appear in sheet 1. e.g., if sheet 1 contains a cell "jackson@test.net" and sheet 2 "jackson@yahoo.com, jackson@test.net" ... the vlookup of the cell in sheet 2 should return "jackson@test.net"

Would appreciate any advice ... oh, I did try the search function for "vlookup substring" ... found several candidates, though many appeared to be using math functions and I presume would only work for numeric values? At least, I couldn't discern how to adapt the existing answers to serve the above need.

 

[netarc]

Thanks to all for the replies again, but I'm afraid I'm not having any luck in getting this to work. fwiw, I haven't yet tried a blind copy/paste, as I'm trying to learn why the array formulas above should work.

So I'm starting with the SEARCH function - I understand that SEARCH(array_range,searchemailcell,1) should return a number if the text in 'searchemailcell' is found in the array_range, but it merely returns #VALUE for me (and yes, I did confirm I entered the formula w/CTRL-SHIFT-ENTER.

e.g., in my case email is contained in column S, and I verified manually that row 8, that is S8 is a single email which does show up in the array_range. The formula I'm using is...

=SEARCH('[Paper Opt-Out Program.xlsx]OptOut'!$H$1:$H$302,S8)

Column H in the worksheet contains the single email address-per-line, i.e. it's "SHEET1" in the above example. Yet this formula, etnered with CSE, returns #VALUE??

 

[netarc]

Thanks, Mark - I was kind of able to get this to work ... when I recreated your sample data set in an empty worksheet, with the following as an array formula:

=IF(SUM(IF(ISNUMBER(FIND(Sheet1!A$2:A$10,A2)),1)),INDEX(Sheet1!A$2:A$10,MIN(IF(ISNUMBER(FIND(Sheet1!A$2:A$10,A2)),ROW(Sheet1!A$2:A$10)-ROW(Sheet1!A$2)+1))),"")

It worked ... even when I moved the data to different cells, as so:
=IF(SUM(IF(ISNUMBER(FIND(Sheet1!A$2:A$10,K2)),1)),INDEX(Sheet1!A$2:A$10,MIN(IF(ISNUMBER(FIND(Sheet1!A$2:A$10,K2)),ROW(Sheet1!A$2:A$10)-ROW(Sheet1!A$2)+1))),"")


But when I translate this to my target spreadsheet w/the following, I don't get the expected result, either with FIND or SEARCH (used latter in case of case comparison issues)

=IF(SUM(IF(ISNUMBER(SEARCH('[Paper Opt-Out Program_gDoc.xlsx]gDoc OptOut Form Entries'!H$1:H$400,S2)),1)),INDEX('[Paper Opt-Out Program_gDoc.xlsx]gDoc OptOut Form Entries'!H$1:H$400,MIN(IF(ISNUMBER(SEARCH('[Paper Opt-Out Program_gDoc.xlsx]gDoc OptOut Form Entries'!H$1:H$400,S2)),ROW('[Paper Opt-Out Program_gDoc.xlsx]gDoc OptOut Form Entries'!H$1:H$400)-ROW('Family Directory'!S$2)+1))),"")

<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" width="85%" cellpadding="2.5px"><tbody><tr><td style="padding:6px">Array Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6" width="100%" cellpadding="2.5px"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">B3</th><td style="text-align:left">{=IF(SUM(IF(ISNUMBER(FIND(Sheet1!A$3:A$8,A3)),1)),
INDEX(Sheet1!A$3:A$8,MIN(IF(ISNUMBER(FIND(Sheet1!A$3:A$8,A3)),ROW(Sheet1!A$3:A$8)-ROW(Sheet1!A$3)+1))),"")}</td></tr></tbody></table>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself</td></tr></tbody></table>

Markmzz

 

[netarc]

Mark, thank you! I was able to adapt your method to meet my needs - I need to run a few more spot-checks to verify, but it looks like this formula (where the arrEmail is the list of single email addresses being compared to, and S3/Z3 are the parent1/2 email addresses (many of them multiple) which I'm 'looking up').

This results in either "paper" or "email" ... I wasn't sure whether concat was the best way to handle looking up both email columns, but it works; although if there's a more efficient way to do this, I'm open to reworking it to make it less calc-heavy - thanks!


=IF(CONCATENATE(IFERROR(INDEX('Paper Opt-Out Program.xlsx'!arrEmail,MATCH(0,--ISERR(FIND('Paper Opt-Out Program.xlsx'!arrEmail,S3)),0)),""),":",IFERROR(INDEX('Paper Opt-Out Program.xlsx'!arrEmail,MATCH(0,--ISERR(FIND('Paper Opt-Out Program.xlsx'!arrEmail,Z3)),0)),""))=":","Paper","Email")

<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" width="85%" cellpadding="2.5px"><tbody><tr><td style="padding:6px">Array Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6" width="100%" cellpadding="2.5px"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">B3</th><td style="text-align:left">{=IFERROR(INDEX(Sheet1!A$3:A$8,MATCH(0,--ISERR(FIND(Sheet1!A$3:A$8,A3)),0)),"")}</td></tr></tbody></table>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself</td></tr></tbody></table>
Markmzz

 

[Richard Schollar]

The Lookup formula variant should be more efficient I believe (although you'd need to test).

 

[markmzz]

Mark, thank you! I was able to adapt your method to meet my needs - I need to run a few more spot-checks to verify, but it looks like this formula (where the arrEmail is the list of single email addresses being compared to, and S3/Z3 are the parent1/2 email addresses (many of them multiple) which I'm 'looking up').

This results in either "paper" or "email" ... I wasn't sure whether concat was the best way to handle looking up both email columns, but it works; although if there's a more efficient way to do this, I'm open to reworking it to make it less calc-heavy - thanks!


=IF(CONCATENATE(IFERROR(INDEX('Paper Opt-Out Program.xlsx'!arrEmail,MATCH(0,--ISERR(FIND('Paper Opt-Out Program.xlsx'!arrEmail,S3)),0)),""),":",IFERROR(INDEX('Paper Opt-Out Program.xlsx'!arrEmail,MATCH(0,--ISERR(FIND('Paper Opt-Out Program.xlsx'!arrEmail,Z3)),0)),""))=":","Paper","Email")

Netarc,

Try this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>S</th><th>T</th><th>Z</th><th>AA</th><th>AB</th><th>AC</th><th>AD</th><th>AE</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;background-color: #C5D9F1;;">P1 Sheet2</td><td style="text-align: right;;"></td><td style="font-weight: bold;background-color: #C5D9F1;;">P2 Sheet2</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;background-color: #C5D9F1;;"></td><td style="font-weight: bold;text-align: right;background-color: #C5D9F1;;"></td><td style="font-weight: bold;text-align: right;background-color: #C5D9F1;;"></td><td style="font-weight: bold;background-color: #F2F2F2;;">Sheet1</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;background-color: #C5D9F1;;">Emails</td><td style="text-align: right;;"></td><td style="font-weight: bold;background-color: #C5D9F1;;">Emails</td><td style="text-align: right;;"></td><td style="font-weight: bold;background-color: #C5D9F1;;">Final Result</td><td style="font-weight: bold;background-color: #C5D9F1;;">P1 Result</td><td style="font-weight: bold;background-color: #C5D9F1;;">P2 Result</td><td style="font-weight: bold;background-color: #F2F2F2;;">Email</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="background-color: #C5D9F1;;">jack@test.net, jack@yahoo.com, jack@zyx.net</td><td style="text-align: right;;"></td><td style="background-color: #C5D9F1;;">jack@yahoo.com, jack@zyx.net</td><td style="text-align: right;;"></td><td style="background-color: #C5D9F1;;">Email</td><td style="background-color: #C5D9F1;;">jack@test.net</td><td style="background-color: #C5D9F1;;"></td><td style="background-color: #F2F2F2;;">jack99@test.net</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="background-color: #C5D9F1;;">jack21@test.net</td><td style="text-align: right;;"></td><td style="background-color: #C5D9F1;;">jack21@test.net, jack22@test.net</td><td style="text-align: right;;"></td><td style="background-color: #C5D9F1;;">Email</td><td style="background-color: #C5D9F1;;"></td><td style="background-color: #C5D9F1;;">jack22@test.net</td><td style="background-color: #F2F2F2;;">jack1@test.net</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="background-color: #C5D9F1;;">jack39@test.net, jack35@test.net</td><td style="text-align: right;;"></td><td style="background-color: #C5D9F1;;">jack39@test.net, jack35@test.net</td><td style="text-align: right;;"></td><td style="background-color: #C5D9F1;;">Paper</td><td style="background-color: #C5D9F1;;"></td><td style="background-color: #C5D9F1;;"></td><td style="background-color: #F2F2F2;;">jack2@test.net</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="background-color: #C5D9F1;;">jack3@test.net, jack1@test.net, jack13@test.net</td><td style="text-align: right;;"></td><td style="background-color: #C5D9F1;;">jack3@test.net, jack1@test.net, jack13@test.net</td><td style="text-align: right;;"></td><td style="background-color: #C5D9F1;;">Email</td><td style="background-color: #C5D9F1;;">jack1@test.net</td><td style="background-color: #C5D9F1;;">jack1@test.net</td><td style="background-color: #F2F2F2;;">jack3@test.net</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="background-color: #C5D9F1;;">nitl34@gmt.com</td><td style="text-align: right;;"></td><td style="background-color: #C5D9F1;;">nitl34@gmt.com</td><td style="text-align: right;;"></td><td style="background-color: #C5D9F1;;">Paper</td><td style="background-color: #C5D9F1;;"></td><td style="background-color: #C5D9F1;;"></td><td style="background-color: #F2F2F2;;">jack22@test.net</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="background-color: #C5D9F1;;">jack2@test.net</td><td style="text-align: right;;"></td><td style="background-color: #C5D9F1;;">jack52@test.net</td><td style="text-align: right;;"></td><td style="background-color: #C5D9F1;;">Email</td><td style="background-color: #C5D9F1;;">jack2@test.net</td><td style="background-color: #C5D9F1;;"></td><td style="background-color: #F2F2F2;;">jack@test.net</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">AB3</th><td style="text-align:left">=IF(<font color="Blue">SUMPRODUCT(<font color="Red">ISNUMBER(<font color="Green">SEARCH(<font color="Purple">arrEmail,S3</font>)</font>)+ISNUMBER(<font color="Green">SEARCH(<font color="Purple">arrEmail,Z3</font>)</font>)</font>),"Email","Paper"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">AE3</th><td style="text-align:left">=Sheet1!A3</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">AC3</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">arrEmail,MATCH(<font color="Green">0,--ISERR(<font color="Purple">SEARCH(<font color="Teal">arrEmail,S3</font>)</font>),0</font>)</font>),""</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">AD3</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">arrEmail,MATCH(<font color="Green">0,--ISERR(<font color="Purple">SEARCH(<font color="Teal">arrEmail,Z3</font>)</font>),0</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">arrEmail</th><td style="text-align:left">=Sheet1!$A$3:$A$8</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[markmzz]

A small modification in my last formula (now two formulas - choose one):

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(arrEmail,S3&","&Z3))),"Email","Paper")

=IF(SUMPRODUCT(1-ISERR(SEARCH(arrEmail,S3&","&Z3))),"Email","Paper")

Markmzz

 

[netarc]

Thanks, I'll give this a shot. Fwiw, the way I was doing it proved incredibly calc-heavy! With 600 rows, it was taking my 4core system 5+ mins to finish calculating?!?

Your alternative looks more efficient, I'll give it a go. I don't necessarily need to generate the email list - so I'm guessing it'll be fine With just using:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(arrEmail,S3&","&Z3))),"Email","Paper")

to keep the calc hit to a minimum?

Btw, I'd love to reason out WHY this works. I did some reading on array formulas, yet I can't figure how this works. I started by trying to deconstruct this, but I couldn't even get the root formula SEARCH(arrEmail,S3)) to work - iirc it resolved to #VALUE on all rows??

 

[netarc]

Oh and just realized I was using FIND last night, which iirc is case sensitive, so I probably got a lot of false negatives. I'll switch to SEARCH, thanks for the heads up on that!

 

[markmzz]

Thanks, I'll give this a shot. Fwiw, the way I was doing it proved incredibly calc-heavy! With 600 rows, it was taking my 4core system 5+ mins to finish calculating?!?

Your alternative looks more efficient, I'll give it a go. I don't necessarily need to generate the email list - so I'm guessing it'll be fine With just using:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(arrEmail,S3&","&Z3))),"Email","Paper")

to keep the calc hit to a minimum?

Btw, I'd love to reason out WHY this works. I did some reading on array formulas, yet I can't figure how this works. I started by trying to deconstruct this, but I couldn't even get the root formula SEARCH(arrEmail,S3)) to work - iirc it resolved to #VALUE on all rows??

I put the others formulas (columns AC and AD) in my example post only for confirmation.

And one of the two formulas that I posted in my last post do the job more efficient.

Look at this example for understand the last formula:

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>S</TH><TH>Z</TH><TH>AA</TH><TH>AB</TH><TH>AC</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="BACKGROUND-COLOR: #c5d9f1; FONT-WEIGHT: bold">P1 Sheet2</TD><TD style="BACKGROUND-COLOR: #c5d9f1; FONT-WEIGHT: bold">P2 Sheet2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1; FONT-WEIGHT: bold"></TD><TD style="BACKGROUND-COLOR: #f2f2f2">Sheet1</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="BACKGROUND-COLOR: #c5d9f1; FONT-WEIGHT: bold">Emails</TD><TD style="BACKGROUND-COLOR: #c5d9f1; FONT-WEIGHT: bold">Emails</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1; FONT-WEIGHT: bold">Final Result</TD><TD style="BACKGROUND-COLOR: #f2f2f2">Email</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="BACKGROUND-COLOR: #c5d9f1">jack@tes4, jack@ya3, jack@zy2</TD><TD style="BACKGROUND-COLOR: #c5d9f1">jack@test, jack@ya6</TD><TD style="TEXT-ALIGN: center">Parts of the formula</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">Email</TD><TD style="BACKGROUND-COLOR: #f2f2f2">jack@test</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">SEARCH(arrEmail,S3&","&Z3)</TD><TD style="TEXT-ALIGN: center">{31;#VALUE!;#VALUE!}</TD><TD style="BACKGROUND-COLOR: #f2f2f2">jack@yah</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">ÉNÚM(SEARCH(arrEmail,S3&","&Z3))),"Email","Paper")</TD><TD style="TEXT-ALIGN: center">{TRUE;FALSE;FALSE}</TD><TD style="BACKGROUND-COLOR: #f2f2f2">jack@zyx</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">--ISNUMBER(SEARCH(arrEmail,S3&","&Z3))</TD><TD style="TEXT-ALIGN: center">{1;0;0}</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">=SUMPRODUCT(--ISNUMBER(SEARCH(arrEmail,S3&","&Z3)))</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: center">******</TD><TD style="TEXT-ALIGN: center">******</TD><TD style="TEXT-ALIGN: center">****</TD><TD style="TEXT-ALIGN: center">******</TD><TD style="TEXT-ALIGN: center">******</TD></TR></TBODY></TABLE>Sheet2


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>AB3</TH><TD style="TEXT-ALIGN: left">=IF(SUMPRODUCT(--ISNUMBER(SEARCH(arrEmail,S3&","&Z3))),"Email","Paper")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>AB7</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--ISNUMBER(SEARCH(arrEmail,S3&","&Z3)))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Workbook Defined Names<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Name</TH><TH style="TEXT-ALIGN: left">Refers To</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>arrEmail</TH><TD style="TEXT-ALIGN: left">=Sheet1!$A$3:$A$5</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

I hope this help you to understand.

Oh and just realized I was using FIND last night, which iirc is case sensitive, so I probably got a lot of false negatives. I'll switch to SEARCH, thanks for the heads up on that!

Finally, the SEARCH si the best way.

Markmzz

 

[markmzz]

A small modification (in yellow) in my last post:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>S</th><th>Z</th><th>AA</th><th>AB</th><th>AC</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;background-color: #C5D9F1;;">P1 Sheet2</td><td style="font-weight: bold;background-color: #C5D9F1;;">P2 Sheet2</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;background-color: #C5D9F1;;"></td><td style="background-color: #F2F2F2;;">Sheet1</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;background-color: #C5D9F1;;">Emails</td><td style="font-weight: bold;background-color: #C5D9F1;;">Emails</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;background-color: #C5D9F1;;">Final Result</td><td style="background-color: #F2F2F2;;">Email</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="background-color: #C5D9F1;;">jack@tes4, jack@ya3, jack@zy2</td><td style="background-color: #C5D9F1;;">jack@test, jack@ya6</td><td style="text-align: center;;">Parts of the formula</td><td style="text-align: center;background-color: #C5D9F1;;">Email</td><td style="background-color: #F2F2F2;;">jack@test</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">SEARCH(arrEmail,S3&","&Z3)</td><td style="text-align: center;;">{31;#VALUE!;#VALUE!}</td><td style="background-color: #F2F2F2;;">jack@yah</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">ISNUMBER(SEARCH(arrEmail,S3&","&Z3))</td><td style="text-align: center;;">{TRUE;FALSE;FALSE}</td><td style="background-color: #F2F2F2;;">jack@zyx</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">--ISNUMBER(SEARCH(arrEmail,S3&","&Z3))</td><td style="text-align: center;;">{1;0;0}</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">=SUMPRODUCT(--ISNUMBER(SEARCH(arrEmail,S3&","&Z3)))</td><td style="text-align: center;;">1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">****</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">AB3</th><td style="text-align:left">=IF(<font color="Blue">SUMPRODUCT(<font color="Red">--ISNUMBER(<font color="Green">SEARCH(<font color="Purple">arrEmail,S3&","&Z3</font>)</font>)</font>),"Email","Paper"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">AB7</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--ISNUMBER(<font color="Red">SEARCH(<font color="Green">arrEmail,S3&","&Z3</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">arrEmail</th><td style="text-align:left">=Sheet1!$A$3:$A$5</td></tr></tbody></table></td></tr></table><br />
Markmzz