Forum Discussion

Bobby_Todorova's avatar
Bobby_Todorova
Copper Contributor
Jul 19, 2022
Solved

lookup formula

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

 

excel
Excel on Mac
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Jul 19, 2022

    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.

     

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jul 19, 2022

    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.

     

Resources