Vlookup on Data with Spaces

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:

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.