Search code examples
excelexcel-formulaconditional-statementsconditional-formatting

Excel to check if comma separated value in cell contains allowed values in list

I am trying to create an excel formula if comma-separated values in the cell doesn't present in the allowed values list then I need to highlight it.

Something like this

enter image description here

Is there any excel formula to achieve something like this.

Solution

  • You could try:

    enter image description here

    Conditional formatting rule applied to =$B$3:$C$7:

    =ISERROR(SUM(MATCH(TEXTSPLIT(B3,","),H$4:H$7,0)))

    Or, without TEXTSPLIT() but with FILTERXML():

    =ISERROR(SUM(MATCH(FILTERXML("<t><s>"&SUBSTITUTE(B3,",","</s><s>")&"</s></t>","//s"),H$4:H$7,0)))