lookup formula

[Bobby_Todorova]

Hello, I need some help. 

in first  column

Salon beauty Sofia

Salon Varna

Salov  nail and pedicure Plovdiv

in Second column 

Sofia

Varna

Plovdiv

I want to match word from second column in first column, and matching  to be removed from first column. Data in first column are 37000 rows, in second are 950 rows. 

Thanks in advance

[#2 · Accepted solution]

@Bobby_Todorova 

=IF(ISNUMBER(MATCH(TRUE,ISNUMBER(SEARCH($B$1:$B$3,A1)),0)),TRIM(SUBSTITUTE(A1,INDEX($B$1:$B$3,MATCH(TRUE,ISNUMBER(SEARCH($B$1:$B$3,A1)),0)),"")),A1)

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021. The formula can be copied down and range $B$1:$B$3 can be adapted as required.

 

[#3]

Thanks so so so much!!!!