Hi, I'm trying to use a text field to exactly match duplicate data from one worksheet where the data is stored horizontally with another where the data is stored vertically using conditional formatting.

I have used the COUNTIF method, MATCH, XMATCH ... Nothing works. Conditional formatting recognises it should be finding something, but it highlights the wrong fields, including items that aren't even in the second list.

Here's the formula entered into conditional formatting on the range 4:4 in sheet 1 that should work, but doesn't: "=countif('sheet 2'A:A, G4)"

What is returned is a sequence of red shaded cells that do not correlate with the data in sheet 2. It seems random, but it is the same shaded cells however I approach it. Some values are in sheet 2, some arent. I just want to see what isn't in sheet 2 so that I can add it without checking each and every one between sheets.