Today, our 'Dueling Podcast' has Three: Mike, Bill, and Isaac show Data Validation, VLOOKUP, and IFERROR in their annual "Trueling Excel" podcast. Follow along with Episode #1651 as Mike and Bill are schooled on the use of Microsoft Excel.
Dueling Excel Podcast #110...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle] Slaying Excel Dragons
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Dueling Excel Podcast #110...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle] Slaying Excel Dragons
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
Bill: Hey, welcome back. It's time for another Trueling Excel podcast.
I'm Bill Jelen from MrExcel.
I will be joined by Mike Girvin from Excel is Fun and our annual Excel is Funner.
This time we're talking about VLOOKUP.
This is episode 113.
Isaac: So this is VLOOKUP and you see these names and bikes, we want them to appear like-- there's going to be drop-down and then there's going to be a bike right there.
Whatever name I put.
So you see those, you go to this and then-- where are you?
There.
Then you-- not any value.
You want List and then we do-- we can do this for faster if you know this.
Oh, right.
You do that and then we get that and then you do OK and then you see there's a drop-down.
After the drop-down, you can do Isaac, Brandon, or Larissa pop-up name.
Brandon, Larissa.
Okay so then you get-- you do this.
You go =V and if you look closely you'll see the VLOOKUP =VLOOKUP( and then-- wait no, no, no.
All right, so we do-- we do that =VLOOKUP(D2 and then-- Mike: What is that?
Is that-- how-- what does the VLOOKUP-- what does the VLOOKUP do with that?
Isaac: It turns it into like this blue D2 and then it's like blue surrounding-- so that's why it's blue.
And then you-- and then you do those =VLOOKUP(D2,A2:B4, insert and then you press 2 =VLOOKUP(D2,A2:B4,2 because the bike is in 2 and then you press Enter and then it does that.
Then you can do that and then you can do that and then you can do that.
Okay-- Mike: That’s pretty amazing.
Isaac: --now I’ll throw it over to MrExcel.
Bill: All right, Isaac, that was a great trick.
Doing a VLOOKUP at what age?
Six?
Mike: Age six.
Bill: That’s amazing.
Alright, so how am I going to improve on that?
That's incredible.
I'm going to do Alt + DL to create the data validation Allow List and the source will be here just like Isaac did there, A2 to A4 but I'm going to add an input message that says, “Hey, select from the list.
Please choose from the list.” All right, so hey so you see that.
Now when I select that cell, it tells them what to do just in case they don't know anything about computers and they don't have any clue what to do.
So now they can choose from a list.
All right, then =VLOOKUP(.
So we're going to talk about different ways to enter formulas.
Isaac used the mouse a lot but I'm going to use the arrow keys.
I'm going to press the left arrow key see which moves the blue dancing marching ants around D2.
=VLOOKUP(D2 I’ll press comma and over here F4 even though we're not going to copy them.
Someday we might.
Comma,2 and then at the end of every single VLOOKUP you should always put False.
Now, hey, we lucked out because this happened to be sorted and so the True was able to work but most of the time we want to put False.
Just as you go through life, Isaac, say if I'm doing a VLOOKUP, I want to end it in False or 0.
There you go.
Let's do a little test here.
We'll choose Bradon and then Larissa.
Isaac: It’s Brandon.
Bill: Is it Brandon?
Isaac: Yes.
Bill: Then it’s spelled wrong and see-- this will work even if the LOOKUP list is sorted descending.
It'll still continue to work with the color False.
Mike: What happens if you change the-- the dad who misspelled Brandon.
It should be an N. What happens if you change the N?
Bill: Then we're going to get an #N/A error right here.
So if we choose that, we had had Bradon there.
Let's break it.
Let’s break it.
This is-- Isaac: There is a kid that I know named Bradon.
Bill: The horrible #N/A error.
That's like the worst evil monster when you're doing VLOOKUPs.
Getting #N/A errors.
All right, sounds fun.
Mike: #N/A. Well, you know, if we were back in 2003, what would we have to do?
We'd have to put the VLOOKUP two times but I think we can just use this great new function =IFERROR and then if it sees an error, it's just sitting in that value argument there, it's either doing the VLOOKUP or giving you an error.
Comma and then value if error.
Hey, I'm going to put something like “” null text string to show nothing or you could type-- let's just see what that looks like.
Or you could type a polite message.
Something like try again.
So now let's try again.
We'll select-- oh, it broke the data validation.
It actually pasted this on top so you'd have to redo it.
Hey let's go over here.
Let's look at a trick Alt + DL and I'm going to go ahead and select the List just like Isaac did right here, click OK.
So now I have my drop-down, right and then you can do your VLOOKUP arrow key comma =VLOOKUP(D2,A2:B4, and I'm going to keep it sorted, right?
I'm going to just put a 2 here for the second column =VLOOKUP(D2,A2:B4,2) but watch this.
What if we wanted to add some names here?
Notice that the data validation is-- always got three names.
If we convert this to a table Ctrl + T click OK.
Now this is a table so if I come to the end and Tab, I can type Timmy and Timmy doesn't have a bike.
No bike.
Is that right, Isaac?
Isaac: Yes.
Mike: And I'm going to put a not happy smiley face because we need to get him a bike.
Now the thing is we did data validation before we converted it to a table so is it going to be there if I click this?
And there it is.
Look at-- there's Timmy with—oh, he doesn't have a bike.
So even the table features got those dynamic ranges but even if you have a range that's not what's there before you convert it to a table, it will still work.
All right, I'm going to throw it back over to Isaac.
You got to say-- Bill: See you next time.
Mike: Close it.
You got to say bye to everyone.
Isaac: Bye.
Mike: How about next time, are we going to do another truel sometime?
Isaac: Yes, sure.
Look, I can even figure out how to stand on the computer.
Mike: Alright, say bye.
Isaac: Bye.
Bill: See you.
I'm Bill Jelen from MrExcel.
I will be joined by Mike Girvin from Excel is Fun and our annual Excel is Funner.
This time we're talking about VLOOKUP.
This is episode 113.
Isaac: So this is VLOOKUP and you see these names and bikes, we want them to appear like-- there's going to be drop-down and then there's going to be a bike right there.
Whatever name I put.
So you see those, you go to this and then-- where are you?
There.
Then you-- not any value.
You want List and then we do-- we can do this for faster if you know this.
Oh, right.
You do that and then we get that and then you do OK and then you see there's a drop-down.
After the drop-down, you can do Isaac, Brandon, or Larissa pop-up name.
Brandon, Larissa.
Okay so then you get-- you do this.
You go =V and if you look closely you'll see the VLOOKUP =VLOOKUP( and then-- wait no, no, no.
All right, so we do-- we do that =VLOOKUP(D2 and then-- Mike: What is that?
Is that-- how-- what does the VLOOKUP-- what does the VLOOKUP do with that?
Isaac: It turns it into like this blue D2 and then it's like blue surrounding-- so that's why it's blue.
And then you-- and then you do those =VLOOKUP(D2,A2:B4, insert and then you press 2 =VLOOKUP(D2,A2:B4,2 because the bike is in 2 and then you press Enter and then it does that.
Then you can do that and then you can do that and then you can do that.
Okay-- Mike: That’s pretty amazing.
Isaac: --now I’ll throw it over to MrExcel.
Bill: All right, Isaac, that was a great trick.
Doing a VLOOKUP at what age?
Six?
Mike: Age six.
Bill: That’s amazing.
Alright, so how am I going to improve on that?
That's incredible.
I'm going to do Alt + DL to create the data validation Allow List and the source will be here just like Isaac did there, A2 to A4 but I'm going to add an input message that says, “Hey, select from the list.
Please choose from the list.” All right, so hey so you see that.
Now when I select that cell, it tells them what to do just in case they don't know anything about computers and they don't have any clue what to do.
So now they can choose from a list.
All right, then =VLOOKUP(.
So we're going to talk about different ways to enter formulas.
Isaac used the mouse a lot but I'm going to use the arrow keys.
I'm going to press the left arrow key see which moves the blue dancing marching ants around D2.
=VLOOKUP(D2 I’ll press comma and over here F4 even though we're not going to copy them.
Someday we might.
Comma,2 and then at the end of every single VLOOKUP you should always put False.
Now, hey, we lucked out because this happened to be sorted and so the True was able to work but most of the time we want to put False.
Just as you go through life, Isaac, say if I'm doing a VLOOKUP, I want to end it in False or 0.
There you go.
Let's do a little test here.
We'll choose Bradon and then Larissa.
Isaac: It’s Brandon.
Bill: Is it Brandon?
Isaac: Yes.
Bill: Then it’s spelled wrong and see-- this will work even if the LOOKUP list is sorted descending.
It'll still continue to work with the color False.
Mike: What happens if you change the-- the dad who misspelled Brandon.
It should be an N. What happens if you change the N?
Bill: Then we're going to get an #N/A error right here.
So if we choose that, we had had Bradon there.
Let's break it.
Let’s break it.
This is-- Isaac: There is a kid that I know named Bradon.
Bill: The horrible #N/A error.
That's like the worst evil monster when you're doing VLOOKUPs.
Getting #N/A errors.
All right, sounds fun.
Mike: #N/A. Well, you know, if we were back in 2003, what would we have to do?
We'd have to put the VLOOKUP two times but I think we can just use this great new function =IFERROR and then if it sees an error, it's just sitting in that value argument there, it's either doing the VLOOKUP or giving you an error.
Comma and then value if error.
Hey, I'm going to put something like “” null text string to show nothing or you could type-- let's just see what that looks like.
Or you could type a polite message.
Something like try again.
So now let's try again.
We'll select-- oh, it broke the data validation.
It actually pasted this on top so you'd have to redo it.
Hey let's go over here.
Let's look at a trick Alt + DL and I'm going to go ahead and select the List just like Isaac did right here, click OK.
So now I have my drop-down, right and then you can do your VLOOKUP arrow key comma =VLOOKUP(D2,A2:B4, and I'm going to keep it sorted, right?
I'm going to just put a 2 here for the second column =VLOOKUP(D2,A2:B4,2) but watch this.
What if we wanted to add some names here?
Notice that the data validation is-- always got three names.
If we convert this to a table Ctrl + T click OK.
Now this is a table so if I come to the end and Tab, I can type Timmy and Timmy doesn't have a bike.
No bike.
Is that right, Isaac?
Isaac: Yes.
Mike: And I'm going to put a not happy smiley face because we need to get him a bike.
Now the thing is we did data validation before we converted it to a table so is it going to be there if I click this?
And there it is.
Look at-- there's Timmy with—oh, he doesn't have a bike.
So even the table features got those dynamic ranges but even if you have a range that's not what's there before you convert it to a table, it will still work.
All right, I'm going to throw it back over to Isaac.
You got to say-- Bill: See you next time.
Mike: Close it.
You got to say bye to everyone.
Isaac: Bye.
Mike: How about next time, are we going to do another truel sometime?
Isaac: Yes, sure.
Look, I can even figure out how to stand on the computer.
Mike: Alright, say bye.
Isaac: Bye.
Bill: See you.
