VLOOKUP Hack #8: Extra Spaces
Here’s a VLOOKUP question for you: What happens when the lookup value includes extra spaces? For example, a few trailing spaces, or leading spaces, or, extra spaces in the middle of the text string. When the lookup value has extra spaces, but the lookup range values don’t, VLOOKUP runs into problems. This leads us to our next hack.
I’ve prepared a video and full write-up for reference.
Let’s try to visualize the issue before we get to the hack. We have a report structure, as shown below.
We need to retrieve the related Cost from Table1, shown below.
So, we write the following formula into D7:
=VLOOKUP(B7, Table1, 2, 0)
And, to our surprise, it returns an error, as shown below.
What’s going on? Is Excel broken? Did I write the formula incorrectly? The formula looks perfect … so why the error? When we inspect the data closely, we see that the lookup values have extra spaces. Specifically, a bunch of trailing spaces.
So, instead of looking up “AB101” Excel is trying to find “AB101 ” (w/trailing spaces). Our 4th argument is 0, and since it can’t find an exact match, it returns #N/A.
So, what are we supposed to do? Go in there and delete all of the trailing spaces by hand? No, that would take too long. This leads to the hack.
Rather than remove the extra spaces by hand, we’ll get an assist from another function, TRIM.
Hack: Use TRIM in the 1st argument
TRIM as you may have guessed removes excess spaces. Namely, leading, trailing, and extra spaces between words.
So, we just modify our formula above by using TRIM in the first argument. Something like this:
=VLOOKUP(TRIM(B7), Table1, 2, 0)
We hit enter, and bam … it works! We push the formula down, and the results are shown below.
The sample file includes the formulas in case you’d like to check it out.
If you have any other fun VLOOKUP Hacks, please share by posting a comment below!
- Sample file: Hack 8.xlsx