I have a google sheet for a sporting club. I am trying to make a set of cells turn red when a player is scheduled to referee their own game. I have a ‘Lookup’ sheet that has team listed with a list of players under each team. We have 6 men’s teams and 5 women’s teams with team numbers M1-6 and W1-5. On the lookup sheet teams are listed as for example “(M1) Tigers” with the team number in brackets before the team name. These are in row 2, below each team is a list of players first and last names. The schedule sheet has games listed for each round with the first name of a player in a cell underneath each game for the referee. Games are listed as for example “M1 v M2”, I would like the cell to turn red if a player is scheduled to ref a game their team is meant to be playing. But I can’t for the life of me work out how to do this.

Further explanation - in the sample sheet there is a lookup tab that contains team and their respective players. The ref roster tab contains the schedule. In the sample sheet teams M1 and M2 are playing and John has been scheduled to referee. But John belongs to team M1 so I would like the cell to turn red.

See the sample spreadsheet.

  • Welcome to Web Applications Stack Exchange. Please edit your question and insert a table of sample data together with another table that shows your manually entered desired results. Also consider sharing a publicly editable sample spreadsheet. There is a blank sheet maker that lets you share safely. Commented Feb 27 at 14:09
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Commented Feb 27 at 14:22

1 Answer 1


Put this formula in Lookup!A20 and copy to the right:

=textjoin("→", true, A3:A17)

Then use this conditional formatting custom formula rule for the range Roster!B3:F:

=len(B3) * search(filter(regexextract(indirect("Lookup!A2:2"), "\w+"), search(B3, indirect("Lookup!A20:20"))), B2)

See the Solution sheet for reference.

See textjoin(), filter(), indirect() and search().

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.