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.

Video

Narrative

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.

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!

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.

13 Comments

  1. Gemma on December 13, 2017 at 7:26 am

    Hi Jeff
    I had never come across Trim before, so thank you for this. Normally I would use :
    =VLOOKUP((B7 & “*”), Table1, 2, 0)

    • Jeff Lenning on December 13, 2017 at 8:26 am

      Hi Gemma … wildcards work awesome with VLOOKUP, and we’ll talk more about them in our next hack 🙂

      Thanks
      Jeff

  2. Geof on December 13, 2017 at 8:20 am

    What if the extra spaces are in Table1? Can we use TRIM in the 2nd argument of VLOOKUP, or do we have to trim the table in advance of the lookup?

    • Jeff Lenning on December 13, 2017 at 8:25 am

      Hi Geof … we can use wildcards, and we cover that in the next hack 🙂

      Thanks
      Jeff

  3. Moe on December 13, 2017 at 1:52 pm

    How did you know where the spaces were?

    Also how can I just get rid of all spaces even between 2 texts?

    I always just use the trim function then copy and paste values then do the vlookup. Never tried it nested. Looking forward.

    Thanks

    • Jeff Lenning on December 13, 2017 at 8:33 pm

      Hi Moe!
      I had to investigate the data to find the trailing spaces, and jim posted a comment here that shows how to use the SUBSTITUTE function to remove all spaces, not just extra spaces like TRIM 🙂
      Thanks,
      Jeff

  4. jim on December 13, 2017 at 8:30 pm

    I often have to lookup vehicle reg nos, which are sometimes entered with spaces but not necessarily in predictable positions
    My trick is to use SUBSTITUTE to “clean” the entry:
    MATCH(SUBSTITUTE(regnoref,” “,””),lookuprange,)
    oh and yeah, I’ve finally got in the habit of INDEX-MATCHing, unless it’s trivial

    • Jeff Lenning on December 13, 2017 at 8:31 pm

      Nice!! Thanks for sharing this great idea 🙂
      Thanks
      Jeff

  5. Leezip on December 27, 2017 at 4:38 am

    Thank you for the information

  6. Diedre Love on January 4, 2018 at 4:14 pm

    Thank you so much for this hack Jeff!!! I encounter this situation all the time and it’s not an efficient use of time to try and locate the formatting issue and reformat to complete the lookup. Used it today in a serious crunch of validating multiple data sources and it was a HUGE help!

    • Jeff Lenning on January 4, 2018 at 4:17 pm

      Welcome 🙂

  7. Dustin on April 22, 2019 at 10:21 am

    Thank you, Gemma!!
    As much as I’d like to use the suggestion posted in the article, my issue is that I am putting a vlookup inside a vlookup so I cannot say specifically what cell the content I am looking up is in. Your solution of adding the & “*” worked like a charm!!!

  8. zubair on September 15, 2020 at 10:20 am

    if we have to do this formula form number to text than how to use it

Leave a Comment