SOLVED

lookup formula

New Contributor

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 Replies
best response confirmed by Bobby_Todorova (New Contributor)
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.

lookup formula.JPG 

Thanks so so so much!!!!