Searching for specific text using a lookup function

[jfox81]

Hey guys, first post....hoping you can help.

I have a list of email addresses on one sheet and in another sheet a column of contact information that has email addresses embedded as part of the text strings in each cell.

What I need to do is use a formula to look up if the email address on my first sheet exists anywhere in the column of the second sheet. Is there a way to do this using a formula?

Thanks!

 

[MrKowz]

Welcome to the forums!

Without knowing the name of the sheets or what ranges you are working with, it is hard to tailor a formula to your needs, but perhaps this can at least get you on the right path:

Excel Workbook
	A	B
1	this is joe@yahoo.com, joe this is	you found joe!
2	dan@yahoo.com	here dan is!
3	where in the world is carmen@sandiego.com	carmen sandiego made it away again!
4
5	E-Mail address to find	carmen@sandiego.com
6	Value returned	carmen sandiego made it away again!
Sheet1

Excel 2003

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

 

[jfox81]

Thanks! I think your solution may be more complicated than what I actually need to accomplish my task. What I have is:

Workbook Y
A
1 john.doe@gmail.com

In another workbook I have a column where each cell has a lot of contact information in it:


Workbook Z
A
1 Jane Smith, 2011 Pike Place, Framingham, MA 201212, jsmith@fake.com
2 John Doe, 324 Cherry Drive, Denver, CO 80234, john.doe@gmail.com
3 Jason Jones, 2566 First Road, NY, NY 10010, jjones@me.com

So I need to look up to see if John Doe's email from Workbook Y appears in column A in Workbook Z (which it does in this example, so I would l like a TRUE value or something like that. And if it doesn't exists, I would like FALSE value)

Does that make sense? Thanks again!

 

[MrKowz]

I see, try the following. It assumes your second workbook is named "Book2.xls" and the range is on Sheet1.

=IF(ISERROR(MATCH(1,IF(NOT(ISERROR(SEARCH(A1,'[Book2.xls]Sheet1'!A1:A3))),1,""),0)),FALSE,TRUE)

Again, confirm entry with CTRL+SHIFT+ENTER.

 

[jfox81]

Thanks. I tried that and it's coming back with a FALSE value even though the email address does exist in my other workbook. Any other ideas?

 

[MrKowz]

Did you:

• Make sure to adjust the ranges to exactly what you have
• Confirm entry with CTRL+SHIFT+ENTER, not just enter? (you will get {brackets} around your formula)