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.
First test- VLOOKUP can’t find it, but FIND can
If you do a FIND and you find the item, but VLOOKUP didn’t, click into the two offending cells and check for spaces. When doing this make sure you don’t click right next to the text. Rather click as far to the right as possible. You will easily see the spaces then.
So below note in cell A10, the cursor was placed far away from the text (to the right) and when we clicked, the blinking icon wasn’t right next to the text but several characters away. You can use your arrow keys to see how many spaces there are after the text. If you did the same thing in A6, you would find no spaces and that’s the reason for the #N/A.
Another way to see this is with the Function Wizard. Note that in the examples, it is clear that the lookup value has spaces behind because the quote (“) is far away from the text whereas the table array has the quotes right against the text (no spaces)
Second test- VLOOKUP can’t find it, but FIND can
Another problem could be ‘numbers’ and ‘text’ i.e. numbers that Excel sees as text. There are lots of reasons for this especially when exporting from a system.
So below cell A8 and D8 are the same, if you did a FIND on student number 100162 you would find it, but the VLOOKUP says it is not there. Although not completely reliable, there is a hint with the one being aligned to the right (number) and one aligned to the left (text).
Either way, if you can see it and VLOOKUP can’t it will in all likelihood be a space or format issue.
How to fix the VLOOKUP problem
There are a few ways to address this.
First and easiest is to create a new lookup column. Best to do it on both the lookup value and table array. You can use TRIM to get rid of any unnecessary spaces.
To change the formats from numbers to text you can use the VALUE and TEXT functions.
All of these options are addressed in the Intermediate Excel course.
Another option is to do a partial match with the VLOOKUP. This is where you mimic the FIND by telling the VLOOKUP to find cells that CONTAIN the lookup as opposed to being exactly the same as the lookup.
This is slightly more advanced and covered in the Advanced Excel course.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.