Google Sheets - Highlighting Cells if they contain text from another range of cells

Question

I want to highlight a cell in a column with a specific color if it contains any of the elements from a range of cells on another sheet.

I believe I need to use 'custom formula is' with the INDIRECT function to reference the range of cells, but cannot seem to produce a working formula that is considered valid for conditional formatting.

For example, I want the cell to be highlighted in red if it contains the name of a "past member", where past members are defined on another sheet in the range 'Past Members'!D5:D18

Could you please help me understand what kind of a custom formula could achieve this?

Answer 1

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.

Answer 2

This worked for me with a little tweaking. My list was on the same sheet, so adjusted the formula to be

=VLOOKUP(A1,INDIRECT("F1:F16"),1,FALSE)=A1

Where a cell in column A is highlighted if a text from F1:F16 is found in that cell.