Something weird is going on here…
You’re trying to compare two lists of data to see what items from ‘List B’ are in ‘List A’. Sounds simple enough, right?
But some of the lookup values are not found even though you can clearly see the value in ‘List B’.
There are several possible reasons for this:
(a) the values aren’t exactly the same (there could be extra spaces) and so the formula returns an #N/A,
(b) the formula option is set to ‘manual’ (easy fix, change to ‘automatic’ –> ‘Formulas’ / ‘Calculation Options’ / ‘Automatic’),
(c) one of the arguments in your formula is incorrect (i.e. the range is pointing to the wrong column) or
(d) there are some tricky custom formats on top of the data making it look different than what the actual value in the cell really is.
Download my Excel file
Download here or via my OneDrive (file 00067)
Watch my YouTube video
What are the ingredients to my solution?
I combined these steps: confirm MATCH function is set-up properly, set cell ALIGNMENT back to default, remove CUSTOM FORMATS.
How did I create the data? In the Excel file (link at top of this post) look at sheet ‘Generate Data’.