How to get rid of invisible characters when using Vlookup?
Imagine you have the table shown below...
And imagine you want to make a simple Vlookup. Write the ZIP code to get the STATE.
So you write a VLOOKUP: =VLOOKUP(A2;!$A$2:$C$6;3;0)
The problems arise when the values you are looking for (2282 and 2162) are on the source table but you get #N/A errors.
VLOOKUP ZIP Table
What is the problem?
Don't give credits to your eyes, if Excel shows a #N/A error is because the value is not there.
So then what to do?
There are invisible characters you don't see on the ZIP Table shown above.
A trick I use is to concatenate a character to the left and right of the cell so I can catch visually that a problem exists.
See the ZIP CHECK column below...
Now that you aware that spaces exist, you need to get rid of them.
The typical solution to this problem is to use a TRIM, SUBSTITUTE function or even the REPLACE command but there are cases where these functions don't work.
This is one of those cases where spaces remain after the application of those functions so I will show to you how to get rid of those invisible spaces....
Substitute the spaces by using this formula:
What does this formula do?
It substitutes spaces by an astheric (*) two times.
Copy and paste values on the left column. After you do it the ZIP CHECK column now must show something like this:
Delete ZIP CHECK and ZIP NO SPACES columns.
Now your Vlookup column is ready for a perfect lookup.
And yes, your Vlookup is perfect now!
- Vlookup Tutorial video
Learn why Vlookup function is important and what a Vlookup is
- Excel Vlookup Tutorial
Comprehensive Vlookup Tutorial shows you how to implement an Excel Vlookup function successfully
- Excel Sumproduct Quick Start Guide
Comprehensive Excel SUMPRODUCT book shows you how to create reports with ease