If any number in one range of cells matches a number in another range of cells, highlight them?

Question

I am trying to find a formula for conditional formatting a specific range of cells.

Screenshot

If any number in the yellow cells (B1:K20) matches any number in the green cells (N1:S30), highlight that number in the yellow cells with a background color. There are and will be duplicates in both the yellow and green cells. I made the cells yellow and green to make it easier to explain my problem, normally they are all blank where the highlighted numbers in B1:K20 will have a green background. Numbers range from 1 to 45.

I used to do this by making a new rule in conditional formatting for every unique number in the green cells but that is becoming very tedious as the amount of numbers in the green cells grow larger.

I have experimented for a couple hours with the MATCH formula in conditional formatting but have gotten very odd results, hoping any of the experts here could help.

Answer 1

Please select B1:K20 and try a CF formula rule of:

=countif(sort(unique(query({$N$1:$N$20;$O$1:$O$20;$P$1:$P$20;$Q$1:$Q$20;$R$1:$R$20;$S$1:$S$20}))),B1)

BEWARE This seems unreliable (or just plain wrong?!)

It seems the response lag for changes might be reduced with the array outside the CF formula, so perhaps in say U1:

=sort(unique(query({$N$1:$N$20;$O$1:$O$20;$P$1:$P$20;$Q$1:$Q$20;$R$1:$R$20;$S$1:$S$20})))

and instead for CF:

=countif($U$1:$U$120,B1)