Non-matching to named range - CF not working as advertised

[Dr. Demento]

I am attempting to highlight any single letter value that is not listed in a named range, as a form of visual data validation.

I have a list of single letters in col K that go from A-Z but I'm only interested in four of them (C|L|U|M), which I've named as a range. Since I apply this to all of col K (I never know how many entries I'll have), I pair it with the condition that a name is present in col B. Below is my CF formula:

=AND(MATCH($K1|CLUM)=FALSE|ISBLANK($B1)=FALSE)

My issue is that none of the letters that are NOT CLUM do not get highlighted (which is the whole point of this CF).

The named range is unsorted and the list being CF'd are sorted by date, not by col K or B. The named range is four cells, with one capital letter in each (no need to discriminate between capital and lowercase). Additionally, I'm unable to specify [match type], as I get the generic "The formula you typed contains an error." (Don't know what to do with that one!)

I've also tried with unequal success:

=AND(ISNA(MATCH($K1|CLUM))| LEN($B1)>0)

Any enlightenment would be most appreciated.

 

[RonB1111]

Try:

=AND(ISERROR(MATCH($K1,CLUM,0)),ISBLANK($B1)=FALSE)

 

[Dr. Demento]

I can't include the match type because of the "The formula you typed contains an error" error. Grrr!

When I omit the match type, it doesn't recognize non-CLUM entries.

 

[V_Malkoti]

So, here is what I understand - Formula should return TRUE if column B has a value and the value in column K matches one of values in range named CLUM. It should return FALSE if either of these conditions is not met.

And for that following formula works:

=AND(NOT(ISBLANK($B1)), NOT(ISERROR(MATCH($K1, CLUM, 0))))

 

[Dr. Demento]

V_Malkoti,

Thank you for putting it so starkly. That helped my thinking - which basically is that I didn't explain it very well

The CF should highlight the cells if:
col B has a value
AND
col K does NOT match any of the CLUM range values.

That notwithstanding, I am unable to use any MATCH formula that includes the Match_Type (0, in this case), even when I use Insert Function!! That's probably a thread all it's own.

Thanks so much!

 

[RonB1111]

Try:

=AND($K1<>"C",$K1<>"L",$K1<>"U",$K1<>"M",ISBLANK($B1)=FALSE)

 

[Dr. Demento]

RonB,

That work great! Thank you!!

I had tried a nested AND(OR($K1<>"C", . . .), ISBLANK($B1)=FALSE) but that didn't work. Looks like I need to work on my logic more

Thank you both for your efforts!

 

[RonB1111]

You're welcome!