Match partial text string in one sheet to another text string in another sheet [Solved]

 Santos -
Hi All,
I have two different sheets. In one sheet, I have last names. In the other sheet, I have a column that has cells that contain the person's last name and also additional text.
I am looking to create a new column that will
1. look at the other sheet's column
2. see if the last name appears in the column of cells
3. if it does, copy that cell into the new column
4. if it does not, return an "x"

I can't seem to find a formula that will allow me to find "some text" within another sheet's cell.
Please help!

Thank you!

System Configuration: Macintosh / Chrome 86.0.4240.111

1 reply

Registration date
Sunday September 12, 2010
Last seen
January 28, 2021
Hi Santos,

Give the following formula a try:
=IF(ISERROR(VLOOKUP("*" & A1 & "*",Sheet2!$A$1:$A$4,1,0)),"x",VLOOKUP("*" & A1 & "*",Sheet2!$A$1:$A$4,1,0))

Best regards,
Trowa, that works perfectly!
Thank you very much!

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!