VLOOKUP is returning zero when the matching record in the lookup table is blank. Bill and Mike duel with various solutions to replace the 0 with "No Price Found".
Transcript of the video:
Bill: Hey welcome back, it's time for another Dueling Excel podcast.
I'm Bill Jelen from MrExcel, will be joined by Mike Girvin from Excel Is Fun. This is our episode 131 - Prevent VLOOKUP From Returning Zero!
Alright, today's question sent in from Brussels, has a data validation here, chooses from the data validation, and has a simple VLOOKUP returning answers.
But, what happens if he selects an item, for example model D, where the price has not been filled in yet, it's blank here?
We're getting a zero, and instead of a zero, you like it to say "Sorry but no price yet." Well hey, I have two quick ideas: quick idea #1 is instead of leaving the cell blank put your text there and then it will be returned, that's the solution #1, CTRL+Z to undo.
The solution #2, CTRL+1, we'll go to Custom Number Format, and for positive numbers we're going to show 0, for negative numbers - 0, and for 0, that's the third zone "Sorry no price yet", that will prevent the 0 from appearing.
Click OK, and then the answer will be "Sorry, no price yet".
If we choose something that is positive, we get the answer, there you go.
Mike, let's see what you have!
Mike: Thanks MrExcel!
Absolutely beautiful, Custom Number Format.
Here's what I love so much about doing these duels, I would have just never thought of using Custom Number formatting to solve this problem, absolutely beautiful.
VLOOKUP returns a zero, no problem, get Custom Number formatting to show the text.
Hey, let's come over to this other sheet here.
Now, my inclination, well, it's not to do Custom Number formatting because I just wouldn't have thought of that, but my inclination is usually to change the table.
So just as an example, if you had a bunch of empty cells, you could highlight that range, hit F5 for Go To, click on Special, and then "Blanks" is the word they use in this dialog box to mean "Hey, empty cell".
So I'm going to click OK, and instantly it highlights all of the empty cells.
That light colored cell is the active cell, to start typing the text you want- whoops!
How about "No Price" or whatever.
Now to populate that whatever we typed in the cell into all of the highlighted cells, hold CTRL+Enter!
Now if we select any of the ones with the text, it returns the text instead.
Now I'm going to CTRL+Z, that would be, my inclination is to fix the actual table.
But if you had to leave them empty, then you can come up here and you could alter the formula.
Now it's a zero that's causing us to want something different than a zero, it's not an error, so we can't use like IFERROR, or ISNA, or those type of functions, so we use the IF.
The problem with a zero though is, we're going to actually have to run this VLOOKUP twice in our formula.
So logical test, it needs a test that goes TRUE or FALSE, so I'm just going to say "Hey, when VLOOKUP is equal to zero, that'll be a logical test", comma, the value of TRUE: "No Price".
So if you have text in- wow, I cannot type- "No Price", right?
Then, the value of FALSE, I'm going to have to run that VLOOKUP a second time.
And so, now if I select G, "No Price".
Here, I should turn this to "Speak cells on Enter: off".
You know, there's always a more than one way to skin the cat, and especially with the IF function and the logical test!
Notice it says =0 , well we can take advantage of the fact that the logical test interprets TRUE and FALSE, but it also interprets 0 as false, meaning the number 0, or any non-zero number, as TRUE.
So watch this, I'm just plopping the VLOOKUP in there, remember, it's returning in this situation either some non-zero number or, for an empty cell, a 0.
So, that right there, comma, value if true.
Well, remember that any non-zero number will give us a TRUE, so CTRL+V for the value of TRUE.
And then if it's FALSE, meaning it comes out to be zero, then we put our text.
All righty, always more than one way, especially with the IF function, and those logical tests.
Alright, throw it back to MrExcel!
Bill: Hey, all right Mike, Go To, Special, Blanks!
I love that trick, didn't even think about it, but by far my favorite is the =IF(VLOOKUP,(VLOOKUP,"No Price" ! This will confuse the heck out of your coworkers, and that's what I love to do.
VLOOKUP if that returns a 0, it's treated as FALSE, and we get the "No Price", what a beautiful thing.
Of course you still have to do the VLOOKUP twice.
But that is a great one.
Alright.
Well hey, I want to thank everyone for stopping by, stop back next week for another Dueling Excel podcast from MrExcel and Excel Is Fun!
Hey Mike, "Ctrl+Shift+Enter", the eBook is out, an amazing, amazing book, full of array formulas.
In fact, I used one of your array formulas in my podcast this week, someone coincidentally sent in a question over the week where they had to extract records with a formula based on 3 criteria, and I pulled out your book, and sure enough it was there.
So I re-appropriated that in my podcast.
Everyone should check out this amazing book on Excel array formulas from Mike "Excel Is Fun" Girvin.
Go to mrexcel.com , click on Excel store, and then eBooks, the print book is being printed right now, it we'll be out probably in a couple of weeks.
Alright.
I'm Bill Jelen from MrExcel, will be joined by Mike Girvin from Excel Is Fun. This is our episode 131 - Prevent VLOOKUP From Returning Zero!
Alright, today's question sent in from Brussels, has a data validation here, chooses from the data validation, and has a simple VLOOKUP returning answers.
But, what happens if he selects an item, for example model D, where the price has not been filled in yet, it's blank here?
We're getting a zero, and instead of a zero, you like it to say "Sorry but no price yet." Well hey, I have two quick ideas: quick idea #1 is instead of leaving the cell blank put your text there and then it will be returned, that's the solution #1, CTRL+Z to undo.
The solution #2, CTRL+1, we'll go to Custom Number Format, and for positive numbers we're going to show 0, for negative numbers - 0, and for 0, that's the third zone "Sorry no price yet", that will prevent the 0 from appearing.
Click OK, and then the answer will be "Sorry, no price yet".
If we choose something that is positive, we get the answer, there you go.
Mike, let's see what you have!
Mike: Thanks MrExcel!
Absolutely beautiful, Custom Number Format.
Here's what I love so much about doing these duels, I would have just never thought of using Custom Number formatting to solve this problem, absolutely beautiful.
VLOOKUP returns a zero, no problem, get Custom Number formatting to show the text.
Hey, let's come over to this other sheet here.
Now, my inclination, well, it's not to do Custom Number formatting because I just wouldn't have thought of that, but my inclination is usually to change the table.
So just as an example, if you had a bunch of empty cells, you could highlight that range, hit F5 for Go To, click on Special, and then "Blanks" is the word they use in this dialog box to mean "Hey, empty cell".
So I'm going to click OK, and instantly it highlights all of the empty cells.
That light colored cell is the active cell, to start typing the text you want- whoops!
How about "No Price" or whatever.
Now to populate that whatever we typed in the cell into all of the highlighted cells, hold CTRL+Enter!
Now if we select any of the ones with the text, it returns the text instead.
Now I'm going to CTRL+Z, that would be, my inclination is to fix the actual table.
But if you had to leave them empty, then you can come up here and you could alter the formula.
Now it's a zero that's causing us to want something different than a zero, it's not an error, so we can't use like IFERROR, or ISNA, or those type of functions, so we use the IF.
The problem with a zero though is, we're going to actually have to run this VLOOKUP twice in our formula.
So logical test, it needs a test that goes TRUE or FALSE, so I'm just going to say "Hey, when VLOOKUP is equal to zero, that'll be a logical test", comma, the value of TRUE: "No Price".
So if you have text in- wow, I cannot type- "No Price", right?
Then, the value of FALSE, I'm going to have to run that VLOOKUP a second time.
And so, now if I select G, "No Price".
Here, I should turn this to "Speak cells on Enter: off".
You know, there's always a more than one way to skin the cat, and especially with the IF function and the logical test!
Notice it says =0 , well we can take advantage of the fact that the logical test interprets TRUE and FALSE, but it also interprets 0 as false, meaning the number 0, or any non-zero number, as TRUE.
So watch this, I'm just plopping the VLOOKUP in there, remember, it's returning in this situation either some non-zero number or, for an empty cell, a 0.
So, that right there, comma, value if true.
Well, remember that any non-zero number will give us a TRUE, so CTRL+V for the value of TRUE.
And then if it's FALSE, meaning it comes out to be zero, then we put our text.
All righty, always more than one way, especially with the IF function, and those logical tests.
Alright, throw it back to MrExcel!
Bill: Hey, all right Mike, Go To, Special, Blanks!
I love that trick, didn't even think about it, but by far my favorite is the =IF(VLOOKUP,(VLOOKUP,"No Price" ! This will confuse the heck out of your coworkers, and that's what I love to do.
VLOOKUP if that returns a 0, it's treated as FALSE, and we get the "No Price", what a beautiful thing.
Of course you still have to do the VLOOKUP twice.
But that is a great one.
Alright.
Well hey, I want to thank everyone for stopping by, stop back next week for another Dueling Excel podcast from MrExcel and Excel Is Fun!
Hey Mike, "Ctrl+Shift+Enter", the eBook is out, an amazing, amazing book, full of array formulas.
In fact, I used one of your array formulas in my podcast this week, someone coincidentally sent in a question over the week where they had to extract records with a formula based on 3 criteria, and I pulled out your book, and sure enough it was there.
So I re-appropriated that in my podcast.
Everyone should check out this amazing book on Excel array formulas from Mike "Excel Is Fun" Girvin.
Go to mrexcel.com , click on Excel store, and then eBooks, the print book is being printed right now, it we'll be out probably in a couple of weeks.
Alright.
