Vlookup on Data with Spaces

Add to Favorites

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.


Excel Function: TRIM(), VLOOKUP()
Downloadable Files: Excel File

Similar Content on TeachExcel
TRIM - Remove Spaces From the Sides of Text and Extra Ones in the Middle in Excel
Tutorial: This allows you to make sure that there are no hidden spaces at the start or end of the te...
Sort Data With Headers in Ascending Order in Excel
Macro: Macro that sorts data that has headers in ascending order in Excel. This macro assumes tha...
Sort Data With Headers in Descending Order in Excel
Macro: This Excel macro sorts data that has headers in descending order. This means that data is ...
Partial Match Lookup with Numbers in Excel
Tutorial: Perform lookups on numbers with partial matches. For instance, find the first number that...
Remove Spaces Between Text in Excel
Tutorial: How to remove spaces from the middle of text in Excel.  This includes removing all spaces ...
How to use Vlookup Across Multiple Worksheets in Excel
Tutorial: This tutorial shows you how to use the Vlookup function across multiple worksheets within ...