conditional formatting – Google Sheets – Highlighting Cells if they contain text from another range of cells


It is always easier to write/suggest formulas if you share a link to a sheet, even if that only contains an adequate amount of sample data.

That said, supposing the cell to which you want to apply the CF is A2, you could try this “Custom formula is” CF rule:

=VLOOKUP(A2,INDIRECT("'Past Members'!D5:D18"),1,FALSE)=A2

Essentially, this means, “Try looking up A2 in the target list. If found, it will equal itself, so TRUE. If not found, the result will be an error, which will not equal A2, so FALSE.”

You are correct that ranges in other sheets must be referenced with INDIRECT.