VLOOKUP not finding a value that is there

You build a VLOOKUP and it gives an #N/A (can’t find it) but you then use the FIND tool and you find what you were looking for! A common problem in Excel. So why is Vlookup not finding a value that is there.

First to understand the difference between how VLOOKUP finds items and how the FIND tool finds them.

FIND looks for any cell that CONTAINS the lookup value. So if you were looking for ABC, it would find it in a cell that contained 123ABC456.

VLOOKUP looks for a cell that matches in the ENTIRE cell. So if you were looking for ABC, then it would ignore 123ABC456.

The most common reason this happens though is due to leading or trailing spaces. Although humans don’t see spaces as characters, a space is a character in Excel. So having a space at the end of a cell is difficult for a human to spot but is never ignored by Excel.

This video covers a few ways to address this problem.