What happens when you enter the perfect VLOOKUP formula and everything returns #N/A? Episode 1122 shows you some of the sneaky reasons why VLOOKUPs fail and what to do about it.
This is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of 377 tips from the book!
This is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Well, VLOOKUP week continues.
Monday we talked about the basic VLOOKUP, and then yesterday what to do when you get a single #N/A. Well now, I have a very frustrating problem, look at this, all of my VLOOKUPs or return #N/As, every single one of them.
And the first thing I do when this happens is, I try and find the value BG33-8, there it is, let me cruise through my table over a year out there.
There it is, it's in both places, what’s going on?
Alright well hey, press F2 over here, and you see, the flashing insertion point is right after the 8.
OK, let's go back to the other one, BG33-8 over here in A2, press F2.
Oh, look at that, there's spaces after the 8, some trailing spaces, very common in a COBOL data set.
Back in the days of COBOL, they would say “OK, item number gets 9 characters or 8 characters or 10 characters.” And if you don't fill it up, they right-pad with spaces, so what we're going to do?
Well, we're going to come here, and instead of looking up A2, there's a great function, do you know what it is?
It’s the TRIM function!
TRIM takes leading and trailing spaces off the data, and now our VLOOKUP starts to work again, BAM, we're great.
Now what if the problem is opposite, what if this one doesn't have the spaces, but our lookup table does have the spaces?
I have an example of that, go on to the next worksheet here, so.
Now this one, press f2, see the insertion point is right up to the 8, and here press F2, there's where we have the extra space as well.
What I would do is, I would come here and say =TRIM, shoot that down, double-click the fill handle.
And then we will just right-click, drag right, say Copy here as Values Only, that gets rid of the spaces, and now you see all of our VLOOKUPs work over here, alright so, very common way to go.
The other place where I've seen this happen is, if we have numbers in one place, and in the other place numbers stored as text.
You need to get rid of those numbers, store this text on one side or the other, to allow your VLOOKUPs to work, so.
Couple of #N/A’s are normal, all #N/A’s, good place to check is to see if there's some leading or trailing spaces.
Unknowingly, upper and lower case has no problems at all, it could be “BG” over here and “bg” over there, it finds a match.
So Excel has no problems with the upper and lower case, but it's the invisible spaces that we can't see that are causing the problem.
Alright hey, thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Well, VLOOKUP week continues.
Monday we talked about the basic VLOOKUP, and then yesterday what to do when you get a single #N/A. Well now, I have a very frustrating problem, look at this, all of my VLOOKUPs or return #N/As, every single one of them.
And the first thing I do when this happens is, I try and find the value BG33-8, there it is, let me cruise through my table over a year out there.
There it is, it's in both places, what’s going on?
Alright well hey, press F2 over here, and you see, the flashing insertion point is right after the 8.
OK, let's go back to the other one, BG33-8 over here in A2, press F2.
Oh, look at that, there's spaces after the 8, some trailing spaces, very common in a COBOL data set.
Back in the days of COBOL, they would say “OK, item number gets 9 characters or 8 characters or 10 characters.” And if you don't fill it up, they right-pad with spaces, so what we're going to do?
Well, we're going to come here, and instead of looking up A2, there's a great function, do you know what it is?
It’s the TRIM function!
TRIM takes leading and trailing spaces off the data, and now our VLOOKUP starts to work again, BAM, we're great.
Now what if the problem is opposite, what if this one doesn't have the spaces, but our lookup table does have the spaces?
I have an example of that, go on to the next worksheet here, so.
Now this one, press f2, see the insertion point is right up to the 8, and here press F2, there's where we have the extra space as well.
What I would do is, I would come here and say =TRIM, shoot that down, double-click the fill handle.
And then we will just right-click, drag right, say Copy here as Values Only, that gets rid of the spaces, and now you see all of our VLOOKUPs work over here, alright so, very common way to go.
The other place where I've seen this happen is, if we have numbers in one place, and in the other place numbers stored as text.
You need to get rid of those numbers, store this text on one side or the other, to allow your VLOOKUPs to work, so.
Couple of #N/A’s are normal, all #N/A’s, good place to check is to see if there's some leading or trailing spaces.
Unknowingly, upper and lower case has no problems at all, it could be “BG” over here and “bg” over there, it finds a match.
So Excel has no problems with the upper and lower case, but it's the invisible spaces that we can't see that are causing the problem.
Alright hey, thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
