OK, having done my own search and testing I've found you can use this formula:
This returns a 1 if there's a match, or a #N/A if there's not a match. Alternatively, to not show an error, use this:
If you want it to show blank or the word MISSING, change the final 1 to "" and the final 0 to "MISSING".
The downside with this approach is that you can't just search on a whole column (e.g. B:B), yu have to use a set range. If you know you have a max number of rows (I've used 2000 in my example), or you're happy to set it to the max possible number of rows, then this isn't a problem.
There are also a number of approaches using SUMPRODUCT, this site has some examples, but be sure to read the comments at the bottom to get the right version of the formula.
Bookmarks