Searching for specific text using a lookup function

jfox81

New Member
Joined
Feb 9, 2011
Messages
3
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

Well-known Member
Joined
Jun 30, 2008
Messages
6,649
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
AB
1this is joe@yahoo.com, joe this isyou found joe!
2dan@yahoo.comhere dan is!
3where in the world is carmen@sandiego.comcarmen sandiego made it away again!
4
5E-Mail address to findcarmen@sandiego.com
6Value returnedcarmen 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

New Member
Joined
Feb 9, 2011
Messages
3
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

Well-known Member
Joined
Jun 30, 2008
Messages
6,649
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

New Member
Joined
Feb 9, 2011
Messages
3
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

Well-known Member
Joined
Jun 30, 2008
Messages
6,649
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)
 

Forum statistics

Threads
1,082,575
Messages
5,366,415
Members
400,888
Latest member
Cdim7

Some videos you may like

This Week's Hot Topics

Top