Forum Discussion

Copper Contributor

Nov 29, 2017

Conditional Formatting - using multiple formulas

Hi guys, a little confused with the formulas in Conditional Formatting I'm trying to highlight cells in Column B where it contains the model # i.e. the string in Column A, which contains both t...

Iron Contributor

Nov 29, 2017

=NOT(ISERROR(MATCH(LEFT(B1,6),$A$1:$A$3,0)))

Select B1, set the conditional formatting to the above formula. After that, set the conditional formatting apply to $B$1:$B$3.

Copper Contributor

Nov 30, 2017

Hi Willy,

Thanks heaps for your response, but we require the formula to be more expanded beyond a 6 character model #, i.e. the model can be any length of characters, not just limited to 6.

(LEFT(B1,6)

Does that make sense?

Willy Lau
Iron Contributor
Nov 30, 2017
yes, that make sense. I am sorry. how about the suffix? Is the suffix fixed length? if yes, you can change

LEFT(B1,6)

to

LEFT(B1,LEN(B1)-LEN(Suffix))
- Alex Hsu
  Copper Contributor
  Nov 30, 2017
  Hi Willy,
  
  Thanks, the suffix is not a fixed length either, and usually is 3-5 characters long.
  - Willy Lau
    Iron Contributor
    Nov 30, 2017
    model
    ABC1234
    ABC123
    
    item
    ABC12345
    
    Can tell me whether the item matches any of the model?
SergeiBaklan
Diamond Contributor
Nov 30, 2017
The rule could be

=SUMPRODUCT(ISNUMBER(SEARCH($A$1:$A$100,$B1))*NOT(ISBLANK($A$1:$A$100)))

or whatever range you have
- Alex Hsu
  Copper Contributor
  Nov 30, 2017
  Hi Sergei,
  
  Thanks for your response, just trying to wrap my head around this formula =P before i put it into use.
  - Willy Lau
    Iron Contributor
    Nov 30, 2017
    I'm also learning from Sergei from all his replies.
- Willy Lau
  Iron Contributor
  Nov 30, 2017
  Thanks Sergei. I still have a question.
  
  does your formula cater the following case
  
  model
  
  TABC123
  
  ITEM
  
  ABC123SUFFIX
  - SergeiBaklan
    Diamond Contributor
    Nov 30, 2017
    Willy, prefix is not ignored, TABC123 won't be found in ABC123SUFFIX

Resources