Search for text within text

[Mashtonsmith]

Hi,

I have a situation where I need to confirm that an email address in column D matches the surname in column A.

Clearly the email address will contain the Surname, First name and the remainder of the email address. I ideally need it to return a true result if column A contains the surname contained within the text of column D.

Help?

 

[MarkAndrews]

=countif(D:D,"a1*") may work?

 

[Jon von der Heyden]

Or if you have a consistent format in the e-mail address, e.g. firstname.lastname@email.com, then perhaps something like this?

Book9
	A	B	C	D
1	E-mail	LastName	Vlookup
2	firstname.lastname@email.com	lastname	Ok
3	jon.vonderheyden@siemens.com	vonderheyden	Ok
4
5
6	Other list
7	lastname
8	vonderheyden
Sheet1

To get the surname: MID(A2,FIND(".",A2)+1,FIND("@",A2)-FIND(".",A2)-1)
The lookup: =IF(ISNA(VLOOKUP(B2,$A$7:$A$8,1,0)),"Error","Ok")

Regards,
Jon

 

[GlennUK]

MarkAndrews,

=countif(D:D,"a1*") may work?

... I'd say no it wouldn't.

 

[Fergus]

Or try: =IF(ISNA(MATCH("*"&A1&"*",D:D,0)),"no match","ok")

 

[Fergus]

Or if you are likely to get blanks in column A try: =IF(OR(ISNA(MATCH("*"&A1&"*",D:D,0)),ISBLANK(A1)),"no match","ok")

 

[MarkAndrews]

MarkAndrews,

=countif(D:D,"a1*") may work?

... I'd say no it wouldn't.

Yeah, I realised after i'd posted I'd forgotten the find bits

The cold i have is clouding my judgement

 

[erik.van.geit]

Hi, all,

alternatively
  A                            B     C  D             
1                                       checklist     
2 firstname.lastname@group.com FALSE    hello         
3 checkme.von.goat@seller.be   TRUE     dear          
4 anotherone.nothing@board.fr  TRUE     correspondent 
5                              FALSE    checkme       
6                                       what          
7                                       other         

test
[Table-It] version 06 by Erik Van Geit

RANGE FORMULA (1st cell)
B2:B7 =NOT(ISNA(LOOKUP(99999,SEARCH($D$2:$D$7,A2),$D$2:$D$7)))

[Table-It] version 06 by Erik Van Geit

REMARK: beware of partial matches like "other"

kind regards,
Erik