SOLVED

# lookup formula

Copper Contributor

# 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.

2 Replies
best response confirmed by Bobby_Todorova (Copper Contributor)
Solution

# Re: lookup formula

``=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.

# Re: lookup formula

Thanks so so so much!!!!
1 best response

Accepted Solutions
best response confirmed by Bobby_Todorova (Copper Contributor)
Solution

# Re: lookup formula

``=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.