I have a Google Sheet that has 2 columns in it. The first column, Column A, is a list of ticket numbers. The second column, Column D, is a list of winning ticket numbers. I would like the list of ticket numbers to have the cell highlighted if it is in the list of winning tickets.

For example, Column A has the row values of 1,2,3,4,5,6,7,8,9,10. Column D has the row values of 3 and 7. So I’d like to use conditional formatting on column A to change the background color of the cells with 3 and 7 to yellow.

I’ve set up a condition formatting rule as follows: Range is A1:A10, Format Rules (Format cells if…) is “Custom formula is”, and the formula I entered is “ifna(match(Address(Row(),COLUMN()), D1:D10), 0) = 0”. Under Formatting Style, I’ve currently set it as the default green background with black letters, but I have tried it with the background fill set to yellow. Clicking the Done button does not produce any error messages.

(Note that I set the formula to be “= 0” so that all but the two matches should have their background fill changed. I’ll change that to “> 0” in the final version.)

My issue is that none of the backgrounds are changed by the conditional formatting. Anyone have an idea or two of things I can try to get this to work? Or anyone have a different was of having this same idea work using a different method?

As a side note, the final version of this sheet will have over 3000 tickets and 90 winning tickets. I am not concerned with using volatile or nonvolatile functions. Either is fine with me. Speed is not something I am concerned with as this sheet is just used by me. I just want something that works. 🙂