Troubleshooting VLOOKUP

Learn Excel from MrExcel podcast, episode 2027 - Troubleshooting VLOOKUP!

Alright, 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, and I always tell this joke in one 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 are 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 is says “Hey, we have an item number W25-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. Alright, in this case, what I want is the 2nd column from that row. And then at the end of every VLOOKUP we have to put either FALSE or 0. Now right here, after I choose the range, I'm going to press the F4 key, and then I want the 2nd column and then FALSE for an exact match. Don't ever choose approximate match, 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 ,TRUE or just leave the ,FALSE off. But every VLOOKUP that you ever create should end in ,FALSE or ,0, 0 is shorter than FALSE, you're supposed to put a FALSE there, but a 0 is the same thing as FALSE.

Alright now, troubleshooting, first thing, when you do a whole bunch of VLOOKUPs, it's very normal to have a couple of #N/As, right? And I Always find the #N/A’s by going Data, Z-A, that brings the #N/As to the top, right there, BG33-9, either that's a typo or it's a brand-new item, alright, and we got to figure it out. So over here to the right I have the new data, I will cut that, and then Alt+I E D, 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 ,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. Alright, so a couple of #N/A’s, extremely, extremely normal, but check this out.

When you start out with answers that work, but then the #N/A’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. Alright see, so here the table is moving as I copy the formula down, right, and so I'm getting lucky in 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 in completely blank cells, and of course nothing is being found. Alright, so that's the first thing, you get a couple that work, a few #N/A’s, a couple more that work, and then all #N/A’s the rest of the way - sure sign that you didn't put the $ in.

Just go back, F2 for Edit mode, select the colon, press F4, that puts all the $ in, double-click to shoot that down, and things start to work again, alright. Next one, exact same formula, the formula is perfect, BG33-8 see, we're getting none of that right. Alright, so I go look, BG33-8, 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, they would say “You know, look, we're going to 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, and the great solution here is to get rid of those leading and trailing spaces with the TRIM function. Instead of A2 use the TRIM(A2), double-click to shoot that down, alright, still the BG33-9 is back in the other table.

Alright, just so you understand how TRIM works, so I typed a bunch of spaces, John, a bunch of spaces, Durran, and a bunch of spaces, and then I concatenated a * before and after so you can see what it looks like. When I ask for the TRIM(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, any doubled-up spaces of the middle become a single space like that. So TRIM, great, great tool in your arsenal, alright, 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 true numbers, and here we have numbers stored as text. And VLOOKUP won't see that as a match, even though 4399, this is a number, this is text, doesn't work. Fastest way to convert a column of text to numbers, select the column, 3 letters in sequence, Alt D E F, and all of a sudden, our VLOOKUPs start to work again, great, great tip from about 1500 podcasts ago. Alright, so those are the most common VLOOKUP problems, either you forgot the $, or you have trailing spaces, or you have numbers and numbers stored as text. All of these tips are in this book “MrExcel XL”, click ihe “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 $ in the lookup table. If there's a few #N/A’s, it's just items missing from the table. If none of the VLOOKUPs work and it’s 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 Alt D E F 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 netcast from MrExcel!