Erin from California shows an unusual way to enter VLOOKUP in today's episode. At least, MrExcel thinks it is unusual. Have you ever seen anyone enter their VLOOKUP this way?
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Learn Excel from MrExcel podcast, episode 1764 - VLOOKUP (A:A WORKS, Implicit Intersection!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. I'm sitting here in the LAX Airport, getting ready to fly home overnight, and I did a seminar today for one of my great clients. I was talking about how to do VLOOKUP, we were looking up some data, and of course, I had this VLOOKUP, right? The =VLOOKUP(A2, we're looking up A2, ItemTable over there, E F G, we want the 3rd column, and ,0 or comma FALSE, either one would work. And Erin(?), one of the ladies in the class, she raised her hand, and Erin had been one of the rock-stars in the class, right? She clearly knows what's going on, she said "Hey, why did you build the VLOOKUP that way?" I said "Well, how did YOU build the VLOOKUP?" And Erin says that she builds her VLOOKUP like this, she chooses this whole column over here, ,ItemTable,3,FALSE , or zero, whichever. I'm like "Erin, that's not going to work." She's like "What do you mean it's not going to work? I do it this way all the time." And several other people from the department backed her up, they do it this way all the time. She was so confident, I'm like "OK, clearly it works, what the heck is going on?" And sure enough, here I'll put it in the formula, we're getting the right answer. It's not trying to do as a VLOOKUP of everything in the column, it's working.
So my question for you "Have you ever seen anyone build your VLOOKUPs like this? Or you haven't?" Instead they're saying "We're looking up A:A!" I was amazed that this worked, and I kind of went on and on and on about it, and Erin finally said "Come on, you're pulling our leg, EVERYONE builds their VLOOKUPs like this!" And I'm like "No! No, I've never seen anyone build their VLOOKUPs like this." Why is it working?
It's working because of something called Implicit Intersection. Implicit Intersection is the situation where if you specify a large range, and the row that you are in intersects with that range, they give you that answer. For example, here's some prices, alright? If I would just ask for =G:G , because I am intersecting with G, they're giving me just the price from this row G, that's what implicit intersection works. A little bit wider, see, it's actually working. And so, the same thing is happening over in that VLOOKUP, we're doing- without even knowing it, we're doing implicit intersection.
When I asked for the VLOOKUP of A:A here, it's not giving me every value in A, just the B11 over there in A10. It works, right?
And is it faster? I don't know, we'd have to ask Charles "FastExcel" Williams whether this is any faster or slower. Erin's argument is "Hey, it's a lot easier to enter, just click on column A, and you're off to the races." So, my question for you, I want you to answer in the YouTube comments: Have you ever seen anyone do VLOOKUP like this? What's your take on it? Let me know.
So hey, shout out to all of my clients there in California, thanks to Erin for that cool tip, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Learn Excel from MrExcel podcast, episode 1764 - VLOOKUP (A:A WORKS, Implicit Intersection!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. I'm sitting here in the LAX Airport, getting ready to fly home overnight, and I did a seminar today for one of my great clients. I was talking about how to do VLOOKUP, we were looking up some data, and of course, I had this VLOOKUP, right? The =VLOOKUP(A2, we're looking up A2, ItemTable over there, E F G, we want the 3rd column, and ,0 or comma FALSE, either one would work. And Erin(?), one of the ladies in the class, she raised her hand, and Erin had been one of the rock-stars in the class, right? She clearly knows what's going on, she said "Hey, why did you build the VLOOKUP that way?" I said "Well, how did YOU build the VLOOKUP?" And Erin says that she builds her VLOOKUP like this, she chooses this whole column over here, ,ItemTable,3,FALSE , or zero, whichever. I'm like "Erin, that's not going to work." She's like "What do you mean it's not going to work? I do it this way all the time." And several other people from the department backed her up, they do it this way all the time. She was so confident, I'm like "OK, clearly it works, what the heck is going on?" And sure enough, here I'll put it in the formula, we're getting the right answer. It's not trying to do as a VLOOKUP of everything in the column, it's working.
So my question for you "Have you ever seen anyone build your VLOOKUPs like this? Or you haven't?" Instead they're saying "We're looking up A:A!" I was amazed that this worked, and I kind of went on and on and on about it, and Erin finally said "Come on, you're pulling our leg, EVERYONE builds their VLOOKUPs like this!" And I'm like "No! No, I've never seen anyone build their VLOOKUPs like this." Why is it working?
It's working because of something called Implicit Intersection. Implicit Intersection is the situation where if you specify a large range, and the row that you are in intersects with that range, they give you that answer. For example, here's some prices, alright? If I would just ask for =G:G , because I am intersecting with G, they're giving me just the price from this row G, that's what implicit intersection works. A little bit wider, see, it's actually working. And so, the same thing is happening over in that VLOOKUP, we're doing- without even knowing it, we're doing implicit intersection.
When I asked for the VLOOKUP of A:A here, it's not giving me every value in A, just the B11 over there in A10. It works, right?
And is it faster? I don't know, we'd have to ask Charles "FastExcel" Williams whether this is any faster or slower. Erin's argument is "Hey, it's a lot easier to enter, just click on column A, and you're off to the races." So, my question for you, I want you to answer in the YouTube comments: Have you ever seen anyone do VLOOKUP like this? What's your take on it? Let me know.
So hey, shout out to all of my clients there in California, thanks to Erin for that cool tip, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
