Conditional formatting to mark certain cells according to what's present in a list + has the correct grouping?

Question

I have an Excel sheet with 4 columns of data (in reality there's more, but for simplicity's sake I shaved it down). Column A denotes a given grouping, say "1" or "2". Assume there will always be at least one grouping. Column B is a random list of words with an arbitrary number of rows. So A1:B1 when taken together might mean "1: cat", A2:B2 is "2: dog", etc.

Columns C and D each hold lists of the words belonging to Group 1 and Group 2, respectively. They each have a header row denoting what group they belong to, then the rest of their rows make up the word lists. Each word list could have any number of words. Words don't have to be unique among lists either; it's often the case that the Group 1 word list is nearly identical to the Group 2 word list, only it has one additional word, or something along those lines.

I need some conditional formatting formulas that would apply to columns C and D. In C, it should mark a cell red if and only if:

• The cell is not blank.
• The cell's word does not show up at all in column B, or...
• The cell's word does show up in column B, but only next to the wrong group. In this case the wrong group would be "Group 2" since column C is for Group 1 words only.

And then in D the same process should apply, but the wrong group number would be 1 instead.

I think covering the third condition would necessarily cover the second condition, I just split them up into two because I could only figure out how to cover the simpler one.

Here's some sample data.

In column C (headed by "Group 1), nothing should be marked red since all words do show up in B alongside the right group number. In column D, everything should be marked red except for "wolf"; even though 4 out of the 6 words show up in B, they only show up next to group 1. And "bear" is simply missing altogether.

With the following formula I can cover the first 2 conditions: =and(NOT(ISBLANK($C1)),ISERROR(MATCH($C1,B:B,0)))

But I'm having trouble approaching how to cover the third condition. I've tried something along the lines of "AND(ISNUM(Match($L1,B:B,0)),ISERROR(MATCH($L$1,$A1,0))))" as an additional condition to the above formula but then nothing gets marked red at all. If anyone could point me in the right direction or has a better way of approaching this problem altogether, I would very much appreciate it.

Answer 1

Maybe like this:

=AND(D3<>"",OR(ISERROR(MATCH(D3,B:B,0)),$A3<>$D$1))

Adjust ranges accordingly.

Answer 2

Only works up to "Group 9" but basic idea is as you describe I think.

Note: Columns F,G are just where I worked on the CF formula before applying it to the range C2:D10. They're not part of the solution.

Edit: You can remove the OR() and the single-column MATCH() if the two-column MATCH() already covers that case.

Simplified CF formula as text:

=LET(GRP,RIGHT(C$1,1)*1, AND(NOT(ISBLANK(C2)), ISERROR(MATCH(GRP&C2,$A2:$A10&$B2:$B10,0))))

Answer 3

Maybe Countifs is a bit easier:

=(COUNTIFS($B:$B,C2,$A:$A,COLUMN(C2)-COLUMN($C2)+1)=0)*(C2<>"")

Here I'm assuming that 'cat' should not be highlighted under Group 2, because although it appears in the wrong group (1), it also appears in the right one (2).