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

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

Is there any excel formula to achieve something like this.

CodePudding user response：

You could try:

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)))

CodePudding user response：

If you have Excel 365 (current channel!) you can use this formula:

=BYROW(A2:A3,LAMBDA(d,
SUM(--ISERROR(FIND(TEXTSPLIT(d,,","),E2:E3)))=0
))

The formula splits - per row - the fruits - and tries to find the value in the allowed list. If found it will return a number, if not an error is returned.

Using -- true/false are converted to 1 and 0.

So that - if all values are found (= no errors) 0 is returned. If a value is not on the allowed list, an error is returned which will result in a sum value > 0.