Match partial text string in one sheet to another text string in another sheet

Santos - Nov 3, 2020 at 02:05 PM
 Santos - Nov 5, 2020 at 12:02 PM
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 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Nov 5, 2020 at 11:25 AM
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!