Small Tweak to formula so it can match words corectly

xtreme07

Board Regular
Joined
Sep 21, 2010
Messages
71
Code:
=IF(ISERROR(LOOKUP(2^15,SEARCH(brands,A1),brands)),0,LOOKUP(2^15,SEARCH(brands,A1),brands))

i would like the formula to consider the space between words

the above formula looks on a cell A1 against a list of words to see if the cell A1 matches a word from the list of brands. if the match if found then it will output that match from brands

issue here is that it matches words that it shouldn't. see below an example

columnA, brands, output - header
adidas group of word1 , adidas, adidas - the result is good
adidasBaskets group of word1 , adidas, adidas - the result is NOT good
adidas Baskets group of word1 , adidas, adidas - the result is good


NOT good is for adidasBaskets
good is for adidas Baskets

so the correction should be for the NOT good.

i'd appreciate a fix for this formula as there are words that trigger a match even tho it's not the case for it.

i would like the formula to consider the space between words
 
Last edited:

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Forum statistics

Threads
1,224,603
Messages
6,179,850
Members
452,948
Latest member
UsmanAli786

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top