Janet wonders why her VLOOKUPs are not working in certain cases. If your key field contains a mix of numbers and text that looks like numbers, then the VLOOKUP won't work. Episode 872 shows how to use the TEXT function to intermittently solve the problem and another method to reliably solve the problem.
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today, a question sent in by Janet.
Janet says, "We're in accounting, we can do VLOOKUPs in our sleep, but sometimes when we get data from other people, the VLOOKUPs seemingly, randomly, don't work.
Like, for example, the other people's data might have leading zeros." Now, I have two different examples here that I set up.
In this first one, they have leading zeros because someone entered this as text-- for example, '00938-- and Janet said, "You know, we change the formatting, we try and copy formats, and it doesn't work at all." Well, if they've really entered this as text, this data's really entered as text-- there's nothing you can do as far as formatting to solve this problem.
Now, if they had gotten this in by using a custom number format-- so I'll do Format Cells, the custom number format of one, two, three, four, five zeros-- 00000-- if that's how that they're adding the leading zeros, the VLOOKUPs are going to work.
So the fact that the VLOOKUPs aren't working, almost, for sure, tells me that what we have is someone has entered the Lookup table as text instead of numbers.
Now, I have two solutions for this.
The first solution is to come here and, instead of doing a VLOOKUP of A4, I want to do a VLOOKUP of the text of A4.
And then, in quotes, put the same number format that's over there in Column D-- so, one, two, three, four, five zeros (00000).
When I do a Lookup of that, all of a sudden might be VLOOKUPs start to work.
But, probably what you're going to find, is in real life, the data over there in Column D is a mix of both text and numbers.
Like, for example, here I'm going to go on...
I envision the scenario where they actually had a Cost Center one, two, three, four, five, and the person entering that data may not have bothered to put the apostrophe.
They're probably just putting the apostrophe to make sure the leading zeros showed up because they didn't know this trick down here.
And you see that that one actually works.
So, now, if we'd use the previous trick of looking up the text of A4, we're going to get hits on most of them but not on the ones where the value we're looking up is not really text.
In that case-- so there we get an N/A down at the bottom.
Alright, so what do we do in this case?
Well, the one way to reliably make sure that this is going to work on both sides, is to convert both sides to numbers.
So I'm going to choose this entire data set-- Data, Text to Columns, and click Finish, and then on this side, Data, Text to Columns, and then click Finish.
Now that I've converted both sides to numbers, all of the N/A should go away, except for this 1255-- well, I had overwritten that value in order to create the fake 1, 2, 3, 4, 5, so that was my introduction in that error.
So, a couple of different ways to handle it.
If you really want to be sure, use Text to Columns to convert both of the columns back to a nice general format where all of the numbers show up as numbers-- you lose your leading zeros, but of course, you can always get them back by using the Custom Format of, in this case, five zeros, to make sure that we pad with zeros.
Well, there you have it, thanks for stopping by, we'll see you next time for another netcast from MrExcel.
[ music ]
Today, a question sent in by Janet.
Janet says, "We're in accounting, we can do VLOOKUPs in our sleep, but sometimes when we get data from other people, the VLOOKUPs seemingly, randomly, don't work.
Like, for example, the other people's data might have leading zeros." Now, I have two different examples here that I set up.
In this first one, they have leading zeros because someone entered this as text-- for example, '00938-- and Janet said, "You know, we change the formatting, we try and copy formats, and it doesn't work at all." Well, if they've really entered this as text, this data's really entered as text-- there's nothing you can do as far as formatting to solve this problem.
Now, if they had gotten this in by using a custom number format-- so I'll do Format Cells, the custom number format of one, two, three, four, five zeros-- 00000-- if that's how that they're adding the leading zeros, the VLOOKUPs are going to work.
So the fact that the VLOOKUPs aren't working, almost, for sure, tells me that what we have is someone has entered the Lookup table as text instead of numbers.
Now, I have two solutions for this.
The first solution is to come here and, instead of doing a VLOOKUP of A4, I want to do a VLOOKUP of the text of A4.
And then, in quotes, put the same number format that's over there in Column D-- so, one, two, three, four, five zeros (00000).
When I do a Lookup of that, all of a sudden might be VLOOKUPs start to work.
But, probably what you're going to find, is in real life, the data over there in Column D is a mix of both text and numbers.
Like, for example, here I'm going to go on...
I envision the scenario where they actually had a Cost Center one, two, three, four, five, and the person entering that data may not have bothered to put the apostrophe.
They're probably just putting the apostrophe to make sure the leading zeros showed up because they didn't know this trick down here.
And you see that that one actually works.
So, now, if we'd use the previous trick of looking up the text of A4, we're going to get hits on most of them but not on the ones where the value we're looking up is not really text.
In that case-- so there we get an N/A down at the bottom.
Alright, so what do we do in this case?
Well, the one way to reliably make sure that this is going to work on both sides, is to convert both sides to numbers.
So I'm going to choose this entire data set-- Data, Text to Columns, and click Finish, and then on this side, Data, Text to Columns, and then click Finish.
Now that I've converted both sides to numbers, all of the N/A should go away, except for this 1255-- well, I had overwritten that value in order to create the fake 1, 2, 3, 4, 5, so that was my introduction in that error.
So, a couple of different ways to handle it.
If you really want to be sure, use Text to Columns to convert both of the columns back to a nice general format where all of the numbers show up as numbers-- you lose your leading zeros, but of course, you can always get them back by using the Custom Format of, in this case, five zeros, to make sure that we pad with zeros.
Well, there you have it, thanks for stopping by, we'll see you next time for another netcast from MrExcel.
[ music ]
