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.

 

[markmzz]

And a did a test with 700 rows and my last formula and it did the job very fast.

Markmzz

 

[netarc]

Ah, so SEARCH & FIND as array functions return ARRAY values themselves? That must be why all I was seeing when I tried them alone was #VALUE?

Thanks for the explanation, going ti read/analyze it later tOnight to see if I can suds this out. Oh, what does the -- prefix to ISNUMBER accomplish, I had googled that yesterday but didn't find amy useful hits.

 

[markmzz]

Ah, so SEARCH & FIND as array functions return ARRAY values themselves? That must be why all I was seeing when I tried them alone was #VALUE?

Thanks for the explanation, going ti read/analyze it later tOnight to see if I can suds this out. Oh, what does the -- prefix to ISNUMBER accomplish, I had googled that yesterday but didn't find amy useful hits.

SEARCH and FIND can return arrays (if you to use a range with argument).

And the function SUMPRODUCT treats the not-numerical entrances of the matrix as if they were zeros, then we need to transforms True in 1 and False in 0.

So, the -- transforms True in 1 and False in 0 like my example.

I think this can help you to uderstand.

Markmzz

 

[T. Valko]

what does the -- prefix to ISNUMBER accomplish, I had googled that yesterday but didn't find amy useful hits.

See this...

http://xldynamic.com/source/xld.SUMPRODUCT.html

 

[netarc]

Ok I think I understand the array formulas, still trying to wrap my head around the AB3 paper/email one, though.

Quick update - I was able to get the new version of the array formulas to work (though still quite slow, so for the time being once I had the info calculated I broke the link); however, the worksheet formula isn't working, it's providing inconsistent results.

Here's my version:
=IF(SUMPRODUCT(ISNUMBER(SEARCH('Paper Opt-Out Program_gDoc.xlsx'!arrEmail,S3))+ISNUMBER(SEARCH('Paper Opt-Out Program_gDoc.xlsx'!arrEmail,AA3))),"Email","Paper")

Note that Z3 became AA3 as I added a column, so the P1/P2 email addresses are now in S3/AA3, respectively.


From what I can telll, if S3/AA3 are _both_ empty for a given row, the sumproduct forumla above calculates to PAPER; if there is any content in either S3/AA3 cell, though, the result is EMAIL (regardless of whether or not the string exists in the lookup list).

<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Worksheet Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px" width="100%"><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">AB3</th><td style="text-align:left">=IF(SUMPRODUCT(ISNUMBER(SEARCH(arrEmail,S3))+ISNUMBER(SEARCH(arrEmail,Z3))),"Email","Paper")</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">AE3</th><td style="text-align:left">=Sheet1!A3</td></tr></tbody></table></td></tr></tbody></table>
<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><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" cellpadding="2.5px" width="100%"><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">AC3</th><td style="text-align:left">{=IFERROR(INDEX(arrEmail,MATCH(0,--ISERR(SEARCH(arrEmail,S3)),0)),"")}</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">AD3</th><td style="text-align:left">{=IFERROR(INDEX(arrEmail,MATCH(0,--ISERR(SEARCH(arrEmail,Z3)),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>
<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Workbook Defined Names<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px" width="100%"><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 style=" background-color: #E0E0F0;color: #161120" width="10px">arrEmail</th><td style="text-align:left">=Sheet1!$A$3:$A$8</td></tr></tbody></table></td></tr></tbody></table>

Markmzz

 

[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?!?

Ok I think I understand the array formulas, still trying to wrap my head around the AB3 paper/email one, though.

Quick update - I was able to get the new version of the array formulas to work (though still quite slow, so for the time being once I had the info calculated I broke the link); however, the worksheet formula isn't working, it's providing inconsistent results.

Here's my version:
=IF(SUMPRODUCT(ISNUMBER(SEARCH('Paper Opt-Out Program_gDoc.xlsx'!arrEmail,S3))+ISNUMBER(SEARCH('Paper Opt-Out Program_gDoc.xlsx'!arrEmail,AA3))),"Email","Paper")

Note that Z3 became AA3 as I added a column, so the P1/P2 email addresses are now in S3/AA3, respectively.


From what I can telll, if S3/AA3 are _both_ empty for a given row, the sumproduct forumla above calculates to PAPER; if there is any content in either S3/AA3 cell, though, the result is EMAIL (regardless of whether or not the string exists in the lookup list).

Look at this:

Note1: The Excel run the two formulas below very fast (only 5 sec).

=IF(SUMPRODUCT((--ISNUMBER(SEARCH(arrEmail,S3)))+(--ISNUMBER(SEARCH(arrEmail,AA3)))),"Email","Paper")

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

Note2: Look at your Inbox (PM).

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><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><TH>AD</TH><TH>AE</TH><TH>AF</TH><TH>AG</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1; FONT-WEIGHT: bold">P1 Sheet2</TD><TD style="TEXT-ALIGN: center">******</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1; FONT-WEIGHT: bold">P2 Sheet2</TD><TD style="TEXT-ALIGN: center">******</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1; FONT-WEIGHT: bold">Formula 1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1; FONT-WEIGHT: bold">Formula 2</TD><TD style="BACKGROUND-COLOR: #f2f2f2">Sheet1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Sheet2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">700 rows to Columns S and AA</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: center; 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">Emails</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1; FONT-WEIGHT: bold">Final Result</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1; FONT-WEIGHT: bold">Final Result</TD><TD style="BACKGROUND-COLOR: #f2f2f2">Email</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Sheet2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">two formulas</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">jack@test0316.net, jack@test0537.com</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">jack@test0587.net, jack@test0072.com</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">Paper</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">Paper</TD><TD style="BACKGROUND-COLOR: #f2f2f2">jack@test0118.net</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Sheet1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">700 rows column A</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">jack@test0588.net</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">jack@test0146.net</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">Email</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">Email</TD><TD style="BACKGROUND-COLOR: #f2f2f2">jack@test0713.com</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Time on a i7 PC</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">5 sec</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">jack@test0513.net, jack@test0059.com, jack@yahoo0333.net</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">jack@test0223.net, jack@test0475.com, jack@yahoo0067.net</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">Paper</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">Paper</TD><TD style="BACKGROUND-COLOR: #f2f2f2">jack@yahoo0030.net</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">jack@test0570.net, jack@test0589.com</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">jack@test0569.net, jack@test0503.com</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">Email</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">Email</TD><TD style="BACKGROUND-COLOR: #f2f2f2">jack@test0293.net</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">jack@test0198.net</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">jack@test0267.net</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">Paper</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">Paper</TD><TD style="BACKGROUND-COLOR: #f2f2f2">jack@test0473.com</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">jack@test0074.net, jack@test0731.com, jack@yahoo0705.net</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">jack@test0086.net, jack@test0019.com, jack@yahoo0529.net</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">Email</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">Email</TD><TD style="BACKGROUND-COLOR: #f2f2f2">jack@yahoo0397.net</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; BACKGROUND-COLOR: #c5d9f1">jack@test0956.net, jack@test0356.com</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">jack@test0968.net, jack@test0745.com</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">Paper</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">Paper</TD><TD style="BACKGROUND-COLOR: #f2f2f2">jack@test0546.net</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">jack@test0252.net</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">jack@test0212.net</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">Paper</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">Paper</TD><TD style="BACKGROUND-COLOR: #f2f2f2">jack@test0737.com</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></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>S3</TH><TD style="TEXT-ALIGN: left">="jack@test"&TEXT(RANDBETWEEN(1,1000),"0000")&".net, "&"jack@test"&TEXT(RANDBETWEEN(1,1000),"0000")&".com"</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>AC3</TH><TD style="TEXT-ALIGN: left">=IF(SUMPRODUCT((--ISNUMBER(SEARCH(arrEmail,S3)))+(--ISNUMBER(SEARCH(arrEmail,AA3)))),"Email","Paper")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>AD3</TH><TD style="TEXT-ALIGN: left">=IF(SUMPRODUCT(--ISNUMBER(SEARCH(arrEmail,S3&","&AA3))),"Email","Paper")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>AE3</TH><TD style="TEXT-ALIGN: left">='C:\[Book2.xlsx]Sheet1'!A3</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>AA3</TH><TD style="TEXT-ALIGN: left">="jack@test"&TEXT(RANDBETWEEN(1,1000),"0000")&".net, "&"jack@test"&TEXT(RANDBETWEEN(1,1000),"0000")&".com"</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">='C:\[Book2.xlsx]Sheet1'!$A$3:$A$703</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Markmzz

 

[netarc]

Thanks, Mark - let me export a sampling of the data i'm working with and I'll send you for testing - thank you for the offer!

 

[markmzz]

Thanks, Mark - let me export a sampling of the data i'm working with and I'll send you for testing - thank you for the offer!

Netarc,

I didn't receive the workbook.

Markmzz