Conditional Formatting a cell if it's value matches the value in a specified range

Question

I'm not the biggest crack when it comes to excel/sheets so this might be a rather easy to solve issue - so sorry for that. I've searched for possible solutions here and on other places for a while now but have not been able to produce something that works. That's why I decided to ask my question here.

What I'm trying to achieve seems rather simple:

This is an organization thing for an MMO. I have several role categories set up in which I list different names of players. Like "Warrior" in I3 and then I list the names of all warrior players under it in the range I4:I9. This continues for other classes in the following columns.

Then I have a "Raid" setup area in which I list some of the players that joined this Raid.

All I want to achieve now is to put up a conditional formatting for the range in the Raid setup area (which is not the issue) and color individual cells depending on their values and their "matches" over in the category area.

Here is a picture of how it looks which might explain it better: https://i.sstatic.net/4qNKJ.png

On the left is the Raid setup. Now for the players listed inside of it the range would be A7:C21.

For this range I want to check if any of the names listed here matches any of the names listed in the range of the "Warriors" column over in I3:I9. In this case it would be "Johnbello" that matches. If that is the case the cell that matches the value - in the Raid setup area - should be colored a specific color.

Where I have been stuck is finding a way not having to define what specifically to look for to make the condition apply rather than taking it out of the defined range the rule should apply to.

The option to put in a "Custom formula is" formula seems to be obvious, I've tried thing's I've found using MATCH and VLOOKUP but seem to fail with the syntax usage apparently. There might be an easier way, or a more reasonable one - but that's why I'm asking here.

I hope this isn't too confusing and makes sense in some way.

Any help would be appreciated!

Answer 1

try:

=REGEXMATCH(A7, TEXTJOIN("|", 1, $I$3:$I))