AND/OR Conditional Formatting with Multiple Criteria

Hey guys,

I have a run into a road block on a particular formula that I am trying to figure out. I believe what I am trying to do would best be suited to use AND/OR functions but I'm not entirely sure how it would work or if that's even the right function to use.

So I am trying to use a conditional formatting formula to highlight cells that compare two different cells text input. This is a little hard to explain but I will give it a try and I will add an example file to hopefully help.

Essentially there a 4 different sets of criteria and each criteria has multiple text options. So if Table 1 shows one of the text options from one of the criteria but Table 2 either shows one of the other criteria's options or is empty it will highlight the cell in Table 1.

There a 4 criteria sets listed below and also on the example file.

Criteria 1: -SHRA, SHRA, +SHRA, -RA, RA, +RA, DZ, VCSH
Criteria 2: -TSRA, TSRA, +TSRA, TSRAGR, VCTS
Criteria 3: FG, BR, HZ
Criteria 4: NSW

So as an example if any of the items from Criteria 1 are in the 1st cell of Table 1 but Table 2 shows one of the items from Criteria 2 then it would highlight Table 1. This would also have to work if Table 1 had anything from Criteria 1, 2, or 3 and Table 2 was empty and vice versa. Criteria 4 should only be correct if both tables show NSW or are empty. Another example would be Table 1 showing NSW and Table 2 shows FG, Table 1 should be highlighted.

I hope that makes sense and if it doesn't please feel free to ask for more clarification.

Thanks for any help!

-Chris

Sorry about that! I have updated the title.

Thank You for changing the title.
This proposal employs two helper columns (E:F) which may be moved and/or hidden for aesthetic purposes.
The helper columns are populated using: =IF(B3="","",INDEX(N$2:Q$2,AGGREGATE(15,6,(COLUMN(N$2:Q$2)-COLUMN(M$2))/(N$3:Q$10=B3),1)))
The conditional formatting (yellow fill) formula is: =OR(E3<>F3,AND(E3="Criteria 4",F3="Criteria 4"),AND(E3="",F3=""))
Let us know if you have any questions.