Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
Vlookup on Data with Spaces
How to use the Vlookup function when your data has extra spaces in it.
This happens when the lookup data is imported with leading or trailing spaces and it will break your formulas and functions.
This tutorial assumes that you are already familiar with Vlookup; if you are not, first read our vlookup tutorial for Excel.
The Problem
Everything should work, but it doesn't, because there is a sneaky space after the word red in cell D5.
The Vlookup that was used above is this:
=VLOOKUP(A2,D4:E6,2,FALSE)
It is a basic Vlookup function that points to the data table in cells D4:E6.
The problem is that the values used to search through this data table have spaces in them for which we need to account.
Look at cell D6, you can see that there is a space in front of the text "blue" and if we go into cell D5, you will see a space after the text "red" so that it in fact reads "red " with a trailing space.
Vlookup Setup that Works When There are Spaces in Your Data
To fix this problem, we need to use the TRIM() function.
Enter the trim function next to the start of the list and reference the first cell in the list; then copy this down the entire list. This creates a new list next to our old list. Then, we simply copy-paste-values over the old list.
Copy the new list:
Select the old list:
Hit Alt + E + S + V Enter:
Delete the old list:
Now, as you may have noticed, you will get a result for "red" and any of the other colors in the list.
Notes
There are other ways of removing these spaces but the one illustrated above is the best solution. It does require a few steps, but it will also work in the largest number of situations. In other words, it is the most robust solution.
Download the accompanying workbook to try everything out.
Question? Ask it in our Excel Forum
Tutorial: This allows you to make sure that there are no hidden spaces at the start or end of the te...
Tutorial: 5 tricks for using Vlookup on data that might not always be 'friendly.' This includes: Re...
Macro: Macro that sorts data that has headers in ascending order in Excel. This macro assumes tha...
Macro: This Excel macro sorts data that has headers in descending order. This means that data is ...
Tutorial: Perform lookups on numbers with partial matches. For instance, find the first number that...
Tutorial: How to remove spaces from the middle of text in Excel. This includes removing all space...