If / IsNumber / Search Complications - Is there a better way?

[LindsJ]

Hi team

I am trying unsuccessfully not to over-complicate something, but end up with two ridiculous lines of formulae to get an accurate result. Surely there must be a better way to do this:

I work with retail data. Now, I have a list of products, call this ColumnB. In this list of products, I want to be able to identify the house brands, which are identified in the actual product names e.g. "HouseBrand Peanut Butter 500GR".

To do this, I have set up a column to input the names of house brands (call this ColumnA), as they vary by each retail category, and thus need to be adjusted by various people for various data.

As such, I now need to search within the text of my product cells in ColumnB, and if it contains any one of the words listed in ColumnA, then return the word HOME (which I can then filter all products on).

Up until this point, I have used quite a string of text to try and accomplish this, but it needs to be versatile to be used by others, and it's asking for trouble to have people digging in a formula that looks like this:

=IF(OR(ISNUMBER(SEARCH("rite",D8)),ISNUMBER(SEARCH("Pnp",D8)),ISNUMBER(SEARCH("Spar",D8)),ISNUMBER(SEARCH("House",D8)),ISNUMBER(SEARCH("Checkers",D8))),"HOME","")

Is there a better way of doing this?

Your help would be much appreciated.

Regards,
LindsJ

 

[Chris Mack]

To shorten it you could say:

=IF(OR(ISNUMBER(SEARCH({"rite","Pnp","Spar","House","Checkers"},D8))),"HOME","")

Hope this helps,

Chris.

 

[Jonmo1]

Perhaps

=IF(SUM(COUNTIF(D8,"*"&{"rite","Pnp","Spar","House","Checkers"}&"*")),"HOME","")

 

[LindsJ]

To shorten it you could say:

=IF(OR(ISNUMBER(SEARCH({"rite","Pnp","Spar","House","Checkers"},D8))),"HOME","")

Hope this helps,

Chris.

Thanks Chris - that did work, and is much simpler.

Is there any way of grabbing those text values from a range instead of manually inputting them into the formula?

It's worth a shot, otherwise this is definitely the simpler version of where I was going with mine.

Thanks heaps,

Regards,
Lindsay

 

[Jon von der Heyden]

You can just throw in a range reference, e.g:


=IF(OR(ISNUMBER(SEARCH(Z1:Z10,D8))),"HOME","")

P.S. I'm rather partial to the COUNTIF method

edit: confirm formula with control+shift+enter... if you use the COUNTIF method then you can substitute the inline array constant with a range reference too. And substiture SUM for SUMPRODUCT and then control+shift+enter is not required.

 

[Jonmo1]

To use a range with the Countifs version..

=IF(SUMPRODUCT(COUNTIF(D8,"*"&E1:E10&"*")),"HOME","")

 

[Chris Mack]

Thanks Chris - that did work, and is much simpler.

Is there any way of grabbing those text values from a range instead of manually inputting them into the formula?

It's worth a shot, otherwise this is definitely the simpler version of where I was going with mine.

Thanks heaps,

Regards,
Lindsay

Yw.

 

[LindsJ]

Thanks, guys.

I am having success with both of the formulae that use manual name input, but neither of the range options give me a consistent result. It often returns HOME even when the cell does not contain any of the names listed in my set.

It's a bit peculiar.

Any advice as to how to tweak this?

Thanks - much appreciated

 

[Chris Mack]

Possibly blank cells in your reference range?

Perhaps using a dynamic named range would be beneficial, e.g.,

=OFFSET(Sheet1!$E$1,0,0,COUNTA(Sheet1!$E$1:$E$10),1)

Chris