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]

If I understand correctly what you want, maybe this formula can help you (array formula - use Ctrl+Shift+Enter and not only Enter):

<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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;background-color: #C5D9F1;;">Sheet2</td><td style="font-weight: bold;text-align: right;background-color: #C5D9F1;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;background-color: #D8D8D8;;">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;;">Result (first match in Sheet1)</td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;background-color: #D8D8D8;;">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="background-color: #C5D9F1;;">jack@test.net</td><td style="text-align: right;;"></td><td style="background-color: #D8D8D8;;">jack99@test.net</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="background-color: #C5D9F1;;">jack21@test.net, jack22@test.net</td><td style="background-color: #C5D9F1;;">jack22@test.net</td><td style="text-align: right;;"></td><td style="background-color: #D8D8D8;;">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="background-color: #C5D9F1;;"></td><td style="text-align: right;;"></td><td style="background-color: #D8D8D8;;">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="background-color: #C5D9F1;;">jack1@test.net</td><td style="text-align: right;;"></td><td style="background-color: #D8D8D8;;">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="background-color: #C5D9F1;;"></td><td style="text-align: right;;"></td><td style="background-color: #D8D8D8;;">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="background-color: #C5D9F1;;">jack2@test.net</td><td style="text-align: right;;"></td><td style="background-color: #D8D8D8;;">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></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></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>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">B3</th><td style="text-align:left">{=IF(<font color="Blue">SUM(<font color="Red">IF(<font color="Green">ISNUMBER(<font color="Purple">FIND(<font color="Teal">Sheet1!A$3:A$8,A3</font>)</font>),1</font>)</font>),
INDEX(<font color="Red">Sheet1!A$3:A$8,MIN(<font color="Green">IF(<font color="Purple">ISNUMBER(<font color="Teal">FIND(<font color="#FF00FF">Sheet1!A$3:A$8,A3</font>)</font>),ROW(<font color="Teal">Sheet1!A$3:A$8</font>)-ROW(<font color="Teal">Sheet1!A$3</font>)+1</font>)</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 />
Markmzz

 

[Richard Schollar]

This will pick up the last match in Sheet1:

<b>Excel 2002</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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;border-top: 1px solid black;background-color: #CCCCFF;;">Sheet2</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;background-color: #CCCCFF;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;background-color: #FFFFFF;;"></td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;background-color: #C0C0C0;;">Sheet1</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;background-color: #CCCCFF;;">Emails</td><td style="font-weight: bold;background-color: #CCCCFF;;">Result (first match in Sheet1)</td><td style="font-weight: bold;text-align: right;background-color: #FFFFFF;;">Last Match</td><td style="font-weight: bold;border-right: 1px solid black;background-color: #C0C0C0;;">Email</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">jack@test.net, jack@yahoo.com, jack@zyx.net</td><td style="background-color: #CCCCFF;;">jack@test.net</td><td style="text-align: right;background-color: #FFFFFF;;">jack@test.net</td><td style="border-right: 1px solid black;background-color: #C0C0C0;;">jack99@test.net</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="background-color: #CCCCFF;;">jack21@test.net, jack22@test.net</td><td style="background-color: #CCCCFF;;">jack22@test.net</td><td style="text-align: right;background-color: #FFFFFF;;">jack22@test.net</td><td style="border-right: 1px solid black;background-color: #C0C0C0;;">jack1@test.net</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="background-color: #CCCCFF;;">jack39@test.net, jack35@test.net</td><td style="text-align: right;background-color: #CCCCFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;">#N/A</td><td style="border-right: 1px solid black;background-color: #C0C0C0;;">jack2@test.net</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">jack3@test.net, jack1@test.net, jack13@test.net</td><td style="background-color: #CCCCFF;;">jack1@test.net</td><td style="text-align: right;background-color: #FFFFFF;;">jack3@test.net</td><td style="border-right: 1px solid black;background-color: #C0C0C0;;">jack3@test.net</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="background-color: #CCCCFF;;">nitl34@gmt.com</td><td style="text-align: right;background-color: #CCCCFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;">#N/A</td><td style=";">jack22@test.net</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="border-bottom: 1px solid black;background-color: #CCCCFF;;">jack2@test.net</td><td style="border-bottom: 1px solid black;background-color: #CCCCFF;;">jack2@test.net</td><td style="text-align: right;background-color: #FFFFFF;;">jack2@test.net</td><td style="border-right: 1px solid black;border-bottom: 1px solid black;background-color: #C0C0C0;;">jack@test.net</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">C3</th><td style="text-align:left">=LOOKUP(<font color="Blue">2^15,SEARCH(<font color="Red">$D$3:$D$8,A3</font>),$D$3:$D$8</font>)</td></tr></tbody></table></td></tr></table><br />

 

[markmzz]

Another way (array formula - use Ctrl+Shift+Enter and not only Enter):

<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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;background-color: #C5D9F1;;">Sheet2</td><td style="font-weight: bold;text-align: right;background-color: #C5D9F1;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;background-color: #D8D8D8;;">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;;">Result (first match in Sheet1)</td><td style="text-align: right;;"></td><td style="font-weight: bold;background-color: #D8D8D8;;">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="background-color: #C5D9F1;;">jack@test.net</td><td style="text-align: right;;"></td><td style="background-color: #D8D8D8;;">jack99@test.net</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="background-color: #C5D9F1;;">jack21@test.net, jack22@test.net</td><td style="background-color: #C5D9F1;;">jack22@test.net</td><td style="text-align: right;;"></td><td style="background-color: #D8D8D8;;">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="background-color: #C5D9F1;;"></td><td style="text-align: right;;"></td><td style="background-color: #D8D8D8;;">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="background-color: #C5D9F1;;">jack1@test.net</td><td style="text-align: right;;"></td><td style="background-color: #D8D8D8;;">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="background-color: #C5D9F1;;"></td><td style="text-align: right;;"></td><td style="background-color: #D8D8D8;;">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="background-color: #C5D9F1;;">jack2@test.net</td><td style="text-align: right;;"></td><td style="background-color: #D8D8D8;;">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></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></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">D3</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">B3</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">Sheet1!A$3:A$8,MATCH(<font color="Green">0,--ISERR(<font color="Purple">FIND(<font color="Teal">Sheet1!A$3:A$8,A3</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 />
Markmzz

 

[T. Valko]

Another way (array formula - use Ctrl+Shift+Enter and not only Enter):


Excel 2007
<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>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

Note that that will only work in Excel 2007 or later. The IFERROR function is only available in Excel 2007 or later.

Also note that FIND is case sensitive. Might be better to use SEARCH.

 

[markmzz]

More another way (array formula - use Ctrl+Shift+Enter and not only Enter):

<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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;background-color: #C5D9F1;;">Sheet2</td><td style="font-weight: bold;text-align: right;background-color: #C5D9F1;;"></td><td style="font-weight: bold;text-align: right;;"></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="font-weight: bold;background-color: #C5D9F1;;">Result (first match in Sheet1)</td><td style="font-weight: bold;text-align: right;;"></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="background-color: #C5D9F1;;">jack@test.net</td><td style="text-align: right;;"></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, jack22@test.net</td><td style="background-color: #C5D9F1;;">jack22@test.net</td><td style="text-align: right;;"></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="background-color: #C5D9F1;;"></td><td style="text-align: right;;"></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="background-color: #C5D9F1;;">jack1@test.net</td><td style="text-align: right;;"></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="background-color: #C5D9F1;;"></td><td style="text-align: right;;"></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="background-color: #C5D9F1;;">jack2@test.net</td><td style="text-align: right;;"></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></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></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>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">B3</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">Sheet1!A$3:A$8,MATCH(<font color="Green">0,--(<font color="Purple">LEN(<font color="Teal">A3</font>)=LEN(<font color="Teal">SUBSTITUTE(<font color="#FF00FF">A3,Sheet1!A$3:A$8,""</font>)</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 />
Do some tests and tell me if it work.

Markmzz

 

[T. Valko]

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.

I'm curious to know why you need to have the lookup value returned?

If all you want is some kind of confirmation that the lookup email address was found then you could do something like this...

Sheet2

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 241px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">email_address_1,email_address_17</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">email_address_2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">email_address_3,email_address_6</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">email_address_4,email_address_22</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">email_address_5</TD></TR></TBODY></TABLE>

Book1
	A	B
2	email_address_11	_
3	email_address_2	Yes
4	email_address_3	Yes
5	email_address_40	_
6	email_address_5	Yes
Sheet1

This formula entered in B2 and copied down:

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(","&A2&",",","&Sheet2!A$2:A$6&",")))),"Yes","")

 

[markmzz]

A small modification in my last two array formulas (if you don't have Excel 2007 or 2010):

=IF(SUM(1-ISERR(FIND(Sheet1!A$3:A$8,A3))),
INDEX(Sheet1!A$3:A$8,MATCH(0,1*ISERR(FIND(Sheet1!A$3:A$8,A3)),0)),"")

=IF(SUM(1-(LEN(A3)=LEN(SUBSTITUTE(A3,Sheet1!A$3:A$8,"")))),
INDEX(Sheet1!A$3:A$8,MATCH(0,1*(LEN(A3)=LEN(SUBSTITUTE(A3,Sheet1!A$3:A$8,""))),0)),"")

Markmzz

 

[netarc]

Thank you for all the responses .... I'm working thru these now, trying to see if I can get one of these to work. To answer this question, though, I don't really need the lookup value to be returned, you're right - a "yes" it was found or "no" would suffice.

re: the example below, though - in my case it needs to be flipped around ... that is, the found/not found column B is in SHEET2. So the results would actually look like this (col B in sheet2 is the output):


Sheet2

<table style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="0" cellpadding="0" cellspacing="0"><colgroup><col style="font-weight:bold; width:30px; "><col style="width:127px;"><col style="width:35px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style=" border-style:solid; border-width:1px; border-color:#000000; ">email_address_1,email_address_17</td><td style="color:#ffffff; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_No</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">email_address_2,email_address_55
</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Yes</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">email_address_3,email_address_6</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Yes</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">email_address_4,email_address_22</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">email_address_5</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Yes</td></tr></tbody></table>

Sheet1

<table style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="0" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:127px;"><col style="width:35px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td> </td><td>A</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style=" border-style:solid; border-width:1px; border-color:#000000; ">email_address_11</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">email_address_2</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">email_address_3</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">email_address_43</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">email_address_5</td></tr></tbody></table>

I'm curious to know why you need to have the lookup value returned?

If all you want is some kind of confirmation that the lookup email address was found then you could do something like this...

Sheet2

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Verdana,Arial; background-color: rgb(255, 255, 255);" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 241px"></colgroup><tbody><tr style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><td>
</td><td>A</td></tr><tr style="HEIGHT: 17px"><td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</td><td style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">email_address_1,email_address_17</td></tr><tr style="HEIGHT: 17px"><td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</td><td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">email_address_2</td></tr><tr style="HEIGHT: 17px"><td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</td><td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">email_address_3,email_address_6</td></tr><tr style="HEIGHT: 17px"><td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</td><td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">email_address_4,email_address_22</td></tr><tr style="HEIGHT: 17px"><td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</td><td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">email_address_5</td></tr></tbody></table>


Sheet1

<table style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="0" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:127px;"><col style="width:35px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td> </td><td>A</td><td>B</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style=" border-style:solid; border-width:1px; border-color:#000000; ">email_address_11</td><td style="color:#ffffff; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">email_address_2</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Yes</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">email_address_3</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Yes</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">email_address_40</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">email_address_5</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Yes</td></tr></tbody></table>

This formula entered in B2 and copied down:

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(","&A2&",",","&Sheet2!A$2:A$6&",")))),"Yes","")

 

[netarc]

ok, to throw another wrinkle into it ... in the example above SHEET2 actually contains two columns of email addresses (one column each for parent1/parent2, where each column could contain 1-3 email address ... yes, I know, it's the pits, but it's the data set I inherited )

So at the end of the day I want the YES/NO for the "found email in SHEET1" to be YES if an email address from either parents' email column shows up in Sheet1.

fwiw, I have created a named range in SHEET2 for the two email address columns, "P1email" and "P2email"