VLOOKUP can not look to the left of the key field!
Use MATCH to find where the item is located, then use INDEX to retrieve the lookup value.
Or - in real life, rearrange the lookup table
This episode features an original letterpress poster from Bobby Rosenstock at justajar.com
Use MATCH to find where the item is located, then use INDEX to retrieve the lookup value.
Or - in real life, rearrange the lookup table
This episode features an original letterpress poster from Bobby Rosenstock at justajar.com
Transcript of the video:
Learn Excel from MrExcel podcast, episode 2029 - VLOOKUP Left?
I see this is tip 31c, it's a bonus tip between 31 and 32, I feel bad about putting this tip in the book, but alright, here we are.
Everything in this book will be in the “MrExcel XL” playlist, click that “i” on the top-right hand corner to get to the whole playlist!
Today we have the bizarre situation where we have to do a VLOOKUP left.
The key field is in E, and what we have to look up is in D, and you cannot say VLOOKUP(A2, “F4” ,-1), no, you cannot do it, although it would be really cool if you could, wouldn’t it?
Alright, and that's why we had Bobby Rosenstock at justAJar design press create this awesome letterpress poster for us, “A value to the left is VLOOKUP’s Kryptonite!
This message brought to you by INDEX & MATCH!” That's right, INDEX and MATCH, yet another way to solve this problem, and here's how it goes.
We use =MATCH, figure out where Alex is in the list, F4, ,0 for an exact match, and that tells us that Alex is in row 3, and for everyone else it tells us where it is.
Now that we know where it is, we're going to use the INDEX function to figure out which department they're in.
So here at the beginning, right after the = sign, =INDEX of these departments, press F4, comma, which one we want?
We want the answer from the MATCH, go to the end of the function, close parenthesis and we should have our answer, and double-click to copy that down, simple!
No, it's not simple, because most people never use MATCH, and most people never use INDEX.
Sure, out of every 100 people I run into who use the VLOOKUP, one or two of us say “Look, INDEX and MATCH is so far superior because it'll go left, right?” And they say “You know, we're going to use VLOOKUP anymore.” But here's the thing, out of that 100 people use VLOOKUP, a 100 of them know how to use VLOOKUP, but only 2 know how to use INDEX and MATCH.
So if you want your spreadsheets to be explanatory, easy to understand by someone else, use VLOOKUP in here.
Let's face it, this is why I feel bad including some tips, here's what we do in real life, Ctrl+X, Ctrl+V, and then just do the VLOOKUP, double-click, easy.
Alright, so there are 40 tips in this book that are awesome, and a bunch of bonus tips, most of them are awesome, this one I don't like it all, but it's in there.
Maybe you just tear that page out, but then you lose the thing on the page behind it, I guess just cross it out or skip that one over, however you want to do it.
But either way, go buy the book, click the “i” on the top-right hand corner.
Alright, this is a short episode: VLOOKUP cannot look to the left of the key field.
So a lot of people say to use a MATCH, and then put that MATCH inside the INDEX, or I say “Just rearrange the lookup table.” Also thanks to Bobby Rosenstock for that cool, cool poster, I think I still have a few of these left, but they were a bonus for the Indiegogo program.
I still have a few of hese left when I offer from around the holidays as a package, the MrExcel playing cards and the posters, so watch for that.
Hey, thanks for stopping by, we’ll see you next time for another netcast from MrExcel!
I see this is tip 31c, it's a bonus tip between 31 and 32, I feel bad about putting this tip in the book, but alright, here we are.
Everything in this book will be in the “MrExcel XL” playlist, click that “i” on the top-right hand corner to get to the whole playlist!
Today we have the bizarre situation where we have to do a VLOOKUP left.
The key field is in E, and what we have to look up is in D, and you cannot say VLOOKUP(A2, “F4” ,-1), no, you cannot do it, although it would be really cool if you could, wouldn’t it?
Alright, and that's why we had Bobby Rosenstock at justAJar design press create this awesome letterpress poster for us, “A value to the left is VLOOKUP’s Kryptonite!
This message brought to you by INDEX & MATCH!” That's right, INDEX and MATCH, yet another way to solve this problem, and here's how it goes.
We use =MATCH, figure out where Alex is in the list, F4, ,0 for an exact match, and that tells us that Alex is in row 3, and for everyone else it tells us where it is.
Now that we know where it is, we're going to use the INDEX function to figure out which department they're in.
So here at the beginning, right after the = sign, =INDEX of these departments, press F4, comma, which one we want?
We want the answer from the MATCH, go to the end of the function, close parenthesis and we should have our answer, and double-click to copy that down, simple!
No, it's not simple, because most people never use MATCH, and most people never use INDEX.
Sure, out of every 100 people I run into who use the VLOOKUP, one or two of us say “Look, INDEX and MATCH is so far superior because it'll go left, right?” And they say “You know, we're going to use VLOOKUP anymore.” But here's the thing, out of that 100 people use VLOOKUP, a 100 of them know how to use VLOOKUP, but only 2 know how to use INDEX and MATCH.
So if you want your spreadsheets to be explanatory, easy to understand by someone else, use VLOOKUP in here.
Let's face it, this is why I feel bad including some tips, here's what we do in real life, Ctrl+X, Ctrl+V, and then just do the VLOOKUP, double-click, easy.
Alright, so there are 40 tips in this book that are awesome, and a bunch of bonus tips, most of them are awesome, this one I don't like it all, but it's in there.
Maybe you just tear that page out, but then you lose the thing on the page behind it, I guess just cross it out or skip that one over, however you want to do it.
But either way, go buy the book, click the “i” on the top-right hand corner.
Alright, this is a short episode: VLOOKUP cannot look to the left of the key field.
So a lot of people say to use a MATCH, and then put that MATCH inside the INDEX, or I say “Just rearrange the lookup table.” Also thanks to Bobby Rosenstock for that cool, cool poster, I think I still have a few of these left, but they were a bonus for the Indiegogo program.
I still have a few of hese left when I offer from around the holidays as a package, the MrExcel playing cards and the posters, so watch for that.
Hey, thanks for stopping by, we’ll see you next time for another netcast from MrExcel!
