Compare 2 columns, return error if mismatch

[virtuosok]

Hi,
I have a list of values in range A2:A20000 (many of them duplicates), and then another list of values in range B2:B200 (all unique)
List of values in column A changes dynamically while column B is static.
I need a formula - if there's one - which would cross-check two columns and return an error message if a value in column A is not found in column B (exact match). In ideal case, I would need the mismatched value listed/called out specifically (if there are several mismatches, it's ok to flag just the first one). However, at the very least I need some kind of error if there's a mismatch to be able to look into this manually...

 

[Fluff]

How about in C2 copied down

=IFNA(MATCH(A2,$B$2:$B$2000,0),"Oops")

 

[virtuosok]

Apologies, one important update now embedded in my post above... column A is by far the longer (and with duplicates)

 

[Fluff]

Then just change the range to B$200 not B$2000

 

[virtuosok]

I'll need to check all 20000 entries from column A against column B (in which case I'll need 20000 cells with formulas) - is there any other way please to make the check more compact? Out of 20000 entries, only 100 or so are unique.

 

[Fluff]

How about using conditional formatting to highlight those that don't match?

 

[virtuosok]

If there's no magic formula I'll do that

 

[Fluff]

If you want to flag col A if it's not in col B how would you do that if you don't want a formula in every cell?