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:


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.


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

Question? Ask it in our Excel Forum

Our Excel Courses

  • Skill Level: All Levels
  • |
  • 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

  • Skill Level: All Levels
  • |
  • 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.

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...
5 Bite-Size Vlookup Tricks for Excel
Tutorial: 5 tricks for using Vlookup on data that might not always be 'friendly.' This includes: Re...
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...
3 Methods to Remove Spaces Between Text in Excel
Tutorial: How to remove spaces from the middle of text in Excel.  This includes removing all space...