I am working on a sheet for a game I play and having some trouble with cell coloring.

I have one sheet, named "PlayerList" which maintains a list of player names, class and role. Players are frequently added/removed from this list. This sheet has the format of:

Column A  Column B  Column C

Name      Class     Role
Player1   Mage      Damage
Player2   Paladin   Heal
Player3   Warrior   Tank

Classes have specific colors associated with them (e.g. Mage = blue, Paladin = pink, Warrior = brown, etc)

I have a second sheet which references the PlayerList sheet, and creates a full list of names with QUERY via the formula:

=QUERY(PlayerList!A3:C5,"SELECT A WHERE C = 'Tank' OR C = 'Heal' OR C = 'Damage'")



I would like to have each player's cell automatically colored depending on their class in PlayerList (e.g. if Player3 is a Warrior, then his name cell is brown). My initial thought was to use multiple Conditional Formatting rules with custom formulas (one for each class color), however, I am not really sure what the formula should be. Is it possible to implement colors when using IF() in a custom formula rule? Or is there a better way to go about this?

The best I could come up with was to make multiple Conditional Formatting rules that check individual player names, however, this is not ideal as I then have to manually adjust the formatting rules every time a player is added/removed from PlayerList.

Here is an example sheet if it helps:


I have manually colored the names on the second sheet, but I would like them to be automated.

1 Answer 1

  • blue:

    =IF(VLOOKUP(E2, INDIRECT("PlayerList!A3:B"), 2, 0)="Mage", 1)

  • pink:

    =IF(VLOOKUP(E2, INDIRECT("PlayerList!A3:B"), 2, 0)="Paladin", 1)

  • orange:

    =IF(VLOOKUP(E2, INDIRECT("PlayerList!A3:B"), 2, 0)="Warrior", 1)


  • 1
    This works perfectly, thanks!
    – meiszen
    Commented Apr 14, 2019 at 14:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.