MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Troubleshooting VLOOKUP

July 25, 2017 - by Bill Jelen

Troubleshooting VLOOKUP

Excel VLOOKUP is powerful, but it won't work when you have specific situations. Today, a look at how to troubleshoot VLOOKUP.

VLOOKUP is my favorite function in Excel. If you can do VLOOKUP, you are able to solve many problems in Excel. But there are things that can trip up a VLOOKUP. This topic talks about a few of them.

But first, the basics of VLOOKUP in plain English.

The data in A:C came from the IT department. You asked for sales by item and date. They gave you Item number. You need Item Description. Rather than wait for the IT department to re-run the data, you find the table shown in column F:G.

Sample Data Set
Sample Data Set

You want VLOOKUP to find the item in A2 while it searches through the first column of the table in $F$3:$G$30. When VLOOKUP finds the match in F7, you want VLOOKUP to return the description found in the second column of the table. Every VLOOKUP that is looking for an exact match has to end in False (or zero, which is equivalent to False). The formula below is set up properly.

VLOOKUP Function
VLOOKUP Function

Notice that you use F4 to add four dollar signs to the address for the lookup table. As you copy the formula down column D, you need the address for the lookup table to remain constant. There are two common alternatives: You could specify the entire columns F:G as the lookup table. Or, you could name F3:G30 with a name such as ItemTable. If you use =VLOOKUP(A2,ItemTable,2,False), the named range acts like an absolute reference.

Any time that you do a bunch of VLOOKUPs, you need to sort the column of VLOOKUPs. Sort ZA, and any #N/A errors will come to the top. In this case, there is one. Item BG33-9 is missing from the lookup table. Maybe it is a typo. Maybe it is a brand-new item. If it is new, insert a new row anywhere in the middle of your lookup table and add the new item.

Sort ZA to Reveal #N/A Errors
Sort ZA to Reveal #N/A Errors

It is fairly normal to have a few #N/A errors. But in the figure below, exactly the same formula is returning nothing but #N/A. When this happens, I see if I can solve the first VLOOKUP. You are looking up the BG33-8 found in A2. Start cruising down through the first column of the lookup table. As you can see, the matching value clearly is in F10. Why can you see this, but Excel cannot see it?

VLOOKUP cannot Find Item
VLOOKUP cannot Find Item

Go to each cell and press the F2 key. Here is F10. Note that the insertion cursor appears right after the 8.

Check List Item Value
Check List Item Value

Here is cell A2 in Edit mode. The insertion cursor is a couple of spaces away from the 8. This is a sign that at some point, this data was stored in an old COBOL data set. Back in COBOL, if the Item field was defined as 10 characters and you only typed 6 characters, COBOL would pad it with 4 extra spaces.

Lookup Value has Space at the End!
Lookup Value has Space at the End!

The solution? Instead of looking up A2, look up the TRIM(A2).

Use TRIM to Remove Space
Use TRIM to Remove Space

The TRIM() function removes leading and trailing spaces. If you have multiple spaces between words, TRIM will convert them to a single space. In the figure below there are spaces before and after both names in A1. =TRIM(A1) removes all but one space in A3.

TRIM to Remove Leading and Trailing Spaces
TRIM to Remove Leading and Trailing Spaces

By the way, what if the problem had been trailing spaces in column F instead of column A? Add a column of TRIM() functions to E, pointing to column F. Copy those and paste as values in F to make the lookups start working again.

The other very common reason that VLOOKUP won’t work is shown here. Column F has real numbers. Column A has text that looks like numbers.

VLOOKUP cannot Match Text with Number
VLOOKUP cannot Match Text with Number

Select all of column A. Press Alt + D, E, F. This does a default Text to Columns and will convert all text numbers to real numbers. The lookup starts working again.

Text to Columns to Convert All Text Numbers to Real Numbers
Text to Columns to Convert All Text Numbers to Real Numbers

Watch Video

  • VLOOKUP solves many problems
  • Common VLOOKUP problems:
  • If VLOOKUP starts out working, but #N/A becomes more prominent: forgot $ in lookup table
  • A few #N/A: items missing from the table
  • None of the VLOOKUP work: check for trailing spaces
  • Remove trailing spaces with TRIM
  • Numbers and numbers stored as text
  • Select both columns and use Alt + D E F
  • Episode includes a joke that both accountants and IT people find funny, but for different reasons

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode
  • 2027 troubleshooting vlookup all right
  • podcasting this entire book click the I
  • on the top right hand corner to get to
  • the playlist vlookup is my favorite
  • function in all of Excel I always tell
  • this joke one in my seminars and it gets
  • a laugh whether you're an IT person or
  • not an IT person I always say well look
  • we have this data set here on the left
  • and I can look at that data and tell
  • that data came from the IT department
  • because it's exactly what I asked for
  • but not really what I needed I asked for
  • item date and quantity and they gave me
  • item number date and quantity but they
  • didn't bother to give me description
  • right and the accountants always laugh
  • at this because this happens to them all
  • the time and the IT guys like well I
  • gave you what you asked for it's not my
  • fault
  • so they both think it's funny for
  • different reasons so you know and the IT
  • guy is busy he can't get back to
  • rewriting the query so we have to do
  • something to rescue this ourselves and
  • so this little table I copied from
  • somewhere else in my computer in plain
  • English what vlookup does it says hey we
  • have an item number W 25-6 we have a
  • table here I want to cruise down through
  • the first column of the table until I
  • find that item number and then return
  • something from that row all right in
  • this case what I want is the second
  • column from that realm and then at the
  • end of every vlookup we have to put
  • either false or zero now right here
  • after I choose the range I'm going to
  • press the f4 key and then I want the
  • second column and then false for an
  • exact match
  • don't ever choose approximate match ever
  • never never never it's not an
  • approximate match it's a very special
  • thing it doesn't work all the time if
  • you like to restate your numbers to the
  • Securities and Exchange Commission by
  • all means feel free to use comma true or
  • just leave the comma falls off but every
  • vlookup that you ever create should end
  • in comma false or comma zero zero is
  • shorter than false you're supposed to
  • put it false there but a zero is the
  • same thing as false all right now
  • troubleshooting first thing when you do
  • a whole bunch of vlookups it's very
  • normal to have a couple of na s right
  • and I always find the NA is by going
  • data Z to a and bring
  • the NA s - the tops are right there BG
  • 33 - 9 either that's a typo or it's a
  • brand-new item all right and we got to
  • figure it out so over here to the rights
  • I have a new data I will cut that and
  • then alt I eat insert those cells in the
  • middle it does not have to be alphabetic
  • this table does not have to be sorted
  • when you're using the comma false
  • version the table isn't you can just put
  • it anywhere you want I didn't put at the
  • end because I didn't have to rewrite the
  • formula I just want the formula to work
  • all right so a couple of na s extremely
  • extremely normal but check this out when
  • you start out with answers that work but
  • then the NA s start to appear slightly
  • frequently and then eventually they
  • appear all the way down that's a sure
  • sign that you didn't lock down the table
  • reference all right see so here the
  • table is moving as I copy the formula
  • down right and so I'm getting lucky and
  • hitting a few that were at the end of
  • the list but eventually I get down to
  • the point where it's looking over here
  • and completely blank cells and of course
  • nothing is being found all right so
  • that's the first thing you get a couple
  • that work a few na s a couple more that
  • work and then all na is the rest of the
  • way sure sign that you didn't put the
  • dollar signs in just go back f2 for edit
  • mode select the colon press f4 that puts
  • all the dollar signs in double-click to
  • shoot that down and things start to work
  • again all right next one exact same
  • formula the formula is perfect BG 33 - 8
  • not see we're getting none of them all
  • right all right so I go look BG 33 - hey
  • there it is it's right there it's in red
  • I should have seen it so I come to this
  • one on the right hand side I press f2
  • and I watch the flashing insertion point
  • and see it's right after the 8 like that
  • and then I come over here and I press f2
  • there are some trailing spaces there
  • this is very very common back in the
  • days of COBOL COBOL they would say you
  • know look we're gonna give you 10 spaces
  • for item number and if you don't type
  • all 10 spaces they will fill the spaces
  • and so
  • this is very common in the and the great
  • solution here is to get rid of those
  • leading and trailing spaces with trim
  • the trim function still instead of a to
  • use the trim of a to double-click to
  • shoot that down or a still the BG 3309
  • is back in the other table all right
  • just so ya how understand how trim works
  • so I typed a bunch of spaces John a
  • bunch of spaces Teran and a bunch of
  • spaces and then I concatenated a
  • asterisk before and after you so you can
  • see what it looks like when I asked for
  • the trim of p4 we get rid of all the
  • leading spaces all the trailing spaces
  • and then the multiple interior spaces
  • are reduced down to one space alright so
  • you can see kind of visualize there that
  • they're getting rid of leading and
  • trailing spaces and he doubled up spaces
  • of them it'll become a single space like
  • that so trim great great tool in your in
  • your arsenal all right here's another
  • really common one if it's not the
  • trailing spaces then the most common
  • thing that I've seen is where over here
  • we have numbers true numbers and here we
  • have numbers stored as text and B lookup
  • won't see that as a match even though
  • for $3.99 this is a number this as text
  • doesn't work fastest way to convert a
  • column of text numbers select the column
  • three letters in sequence alt d e f and
  • all of a sudden our vlookups start to
  • work again great great tip from about
  • 1,500 podcasts and go alright so those
  • are the the most common most common if
  • you look up problems either you forgot
  • the dollar signs or you have trailing
  • spaces or you have numbers and numbers
  • stored as text all of these tips are in
  • this book MrExcel excel all of these
  • tips are in this book MrExcel excel
  • click the I on the top right hand corner
  • you can buy the book alright quick recap
  • vlookup solves many problems if a
  • vlookup starts working but n/a becomes
  • more prominent you forgot to put the
  • dollar sign in the lookup table if
  • there's a few na s it's just items
  • missing from the table if none of the
  • vlookups work and text check for
  • trailing spaces you can use the trim
  • function if you have numbers and numbers
  • stored as text select either column the
  • one that has the text then do all de
  • have to
  • all those back to numbers alright well
  • hey I want to thank you for stopping by
  • we'll see you next time for another net
  • cast from MrExcel

Download File

Download the sample file here: Podcast2027.xlsx

Title Photo: radekkulupa / Pixabay