Apply conditional formatting in Google Docs if current cell exists in a range

6
votes

Very simply, I am attempting to highlight a cell if that cell matches (exactly) with a different cell from a different column (in a different sheet, specifically).

For example, I have an 'Active' and 'Inactive' sheet in my Google Docs Spreadsheet. Some of the items listed in 'Inactive' are also listed in 'Active', and I need to highlight those.

What I've gotten so far is this (that doesn't work):

  • Format -> Conditional Formatting
  • Format Cells If -> Custom Formula Is
  • GT(MATCH(A1, 'Active'!A2:A, 0), 0)

The general formula above works if I use it in the spreadsheet normally and correctly pass the 1st parameter to MATCH(), however when I attempt to move the regular formula to a conditional formatting, it seems to break down: I need to pass the current cell's contents as the 1st parameter, not A1 statically. If using GT() + MATCH() is indeed the correct way to accomplish this, I need a way to express:

GT(MATCH(  'Inactive'! [A + ROW()]  , 'Active'!A2:A, 0), 0)

... Where A+ROW ends up being the non-literal expression, -- i.e. on row 123 this would refer to the cell contents of A123.

There may be also an easier and more intuitive way to do this altogether. To state my problem in a different way, "If the current cell's data [a string] is present (or 'matches') in the range 'Active'!A2:A then highlight it".

1
google-sheetsgoogle-docsgs-conditional-formatting
If you are going to downvote my question, suggestions to improve the question or reasons as to such would be helpful in the future! – A.B. Carroll

1 Answers

7
votes

The problem is that when you are using a different sheet in conditional formatting you need to pass it using INDIRECT(). Use this formula in conditional formatting:

=GT(MATCH(A1, INDIRECT("'Active'!A2:A"), 0), 0)

And apply it to range A1:A. You do not need to pass the row number, google does it for you.