Conditional Formatting of duplicate cells in another sheet in Google Sheets

Question

What I am trying to do is this: Sheet1 is a table of unicode CJK characters that I have arranged in a certain way, and Sheet2 is an imported table of unsorted characters. I want to use conditional formatting to highlight the cells in Sheet2 that already exist in Sheet1, with each cell containing one character. In other words, I want ANY cell in Sheet2 that is also in Sheet1 highlighted.

I am using the custom formula with =countif(Sheet1NamedRange1, A1)>0 on the entire sheet, but it is not doing much. The match function would be perfect if it could use a two-dimensional range rather than a 1 dimensional range.

I may be on the wrong path entirely though. Is there any functions that can do this without hard-coding row by row using match?

player0 player0 · Accepted Answer · 2019-12-03T16:44:30

let J column be on Sheet1 then all you need to use is:

=REGEXMATCH(B2,TEXTJOIN("|",1,INDIRECT("Sheet1!J:J")))

Conditional Formatting of duplicate cells in another sheet in Google Sheets

1 Answers