In today's dueling Excel podcast, how to do a VLOOKUP left. In Episode 1060 Bill and Mike show differing methods, from INDEX and MATCH to LOOKUP.
This is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of 377 tips from the book!
This is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of 377 tips from the book!
Transcript of the video:
Hey welcome, back it is another dueling Excel podcast.
I'm Bill Jelen. Now, today we're going to talk about VLOOKUP, but a really weird situation with VLOOKUP; VLOOKUP of course I think is the most powerful function in all of Excel.
We can use VLOOKUP to go grab for example a description based on SKU, so here we have a drop down where they choose and SKU and then this VLOOKUP is grabbing the description from this table over here in L and M, now as you know VLOOKUP requires the key field to be in the leftmost column and then the information trying to get to the right of that, but sometimes the data set up another way and here to get this price the prices to the left of SKU, now the easiest solution of course just move the price to the right but sometimes you don't have control over this data it might be someone else's data and it's set up wrong, how can we do that?
Well I'm going to show how to use two functions to replace the VLOOKUP, boy wouldn't it be great note to Microsoft in case you're watching to be able to say that we wanted the negative one column instead of saying we want the second column state -1, problem solve life would be good unfortunately it doesn't work that way so we have to use two other functions, in these functions; I love this solution because both of these functions seem completely useless you would never use either one of them.
The first function is called MATCH and if you understand VLOOKUP you understand MATCH say MATCH BR-15-3 in this column of numbers, Comma False or Comma 0, just like VLOOKUP and that will find the exact match but what it does when it finds it, it tells you what row it’s on and not even the real row number the relative row number, so for example BR15-3 is right here well that is the 1, 2, 3, 4, 5, 6, 7th item in that range of cells, I mean what good is that when would anyone ever know what item number a particular value is found it's just insane I originally read about MATCH; I'm like I'll never ever use this but we're going to use that match result with another function called INDEX, INDEX says hey we have a bunch of prices over here and oh by the way we want the value that occurs at a certain row within that range and optionally a certain column now we don't need to specify the column here because there's only one column so check this out; we start out Equal INDEX of the prices in K2 to K29 and then the INDEX function says well which road do you want within that well I don't know what row I want, but I'm going to use another function called MATCH, go grab C2, figure out where C2 occurs in L2 to L29 and we want an exact match, Comma FALSE and that will return the result, so BR15-3 sure enough 379 dollars let's try it, let's do something else BG33-8 is 125 dollars, there you have it it's working cool thing about MATCH . Now with VLOOKUP, either but Comma TRUE or Comma FALSE, the Comma 0 in the MATCH says we want an exact match there's two other options though, you can put a 1 or a -1 and that says hey if the data is sorted then I want the value just below it or just above it in case there's not an exact match, so a little bit more things you can do with MATCH although to be honest every time I do it I put Comma FALSE or Comma 0 because I'm always looking for the exact match.
Now this is the method I use all the time let's see what Mike has, we'll throw it over to Mike.
Mike Grivin (03:45): Thanks MrExcel, hey I like this formula right here and I'll just show you right off the bat why MrExcel is going to get the point right here this is MATCH, so I’m going to delete this and then type a comma there's the drop down in 2007 you can do exact MATCH; one which is less than which would be just like VLOOKUP and then MATCH has this other option as MrExcel.
I'll mention -1 that does greater than, so total versatility with the INDEX and MATCH, ah so INDEX MATCH, Exact Match, let's go ahead and what I'm going to do is use the LOOKUP function, equals LOOKUP , we still have to look up our value, Comma, but look at this unlike the VLOOKUP which has the lookup column and then the retrieval column next to each other you have two arguments here in the LOOKUP; these arguments the LOOKUP vector we're going to say is SKU here and Comma, then we have a result vector the great thing about both of these arguments is that they can be orientated anyway, so right now we have SKU and then to the left we have our retrieval column, but they could be one horizontal, one vertical, they could be any setup any orientation I'm going to close parentheses and then ENTER, now let's just go ahead and test this; click the drop-down oh that's looking great that's looking, oh now there's a problem here and INDEX and MATCH got it right but LOOKUP didn't, the problem is LOOKUP only does approximate match and if this is not sorted in ascending order, now we all know from LOOKUP what ascending order means; you know when we're doing numbers we have smallest to biggest but this is, these are words, these are letters, so the solution if you're allowed to sort the column then you can use LOOKUP, in 2007 I'm simply going to well there's field names at the top and rows and records, we have our little database in essence, in 2007 you can just right-click the field and sort, sort and I'm going to say A to Z, now all the Bs, all the Cs, etc in alphabetical order, now LOOKUP and this INDEX MATCH set up will return the same value every single time, so LOOKUP, great you only have one function if you can sort your LOOKUP column otherwise, INDEX MATCH much more versatile function because of that lost argument.
All right there's a point for MrExcel, all right see you next trip.
Bill Jelen (06:35): Mike that is brilliant, you know I love these dueling Excel podcast because you come up with completely different ways of attacking things, I always thought that LOOKUP was just something left over for a long, long time ago.
But, the fact that you can have a LOOKUP vector and a result vector and I want to put a challenge out there to the people watching, does anyone have a real life example where you have a LOOKUP vector that's vertical and a result vector that's horizontal?
I'm trying to figure out a great example where I could use to show that in my seminars that would be so cool, now the one other thing I need to caution you about is that LOOKUP because it's doing an approximate match is going to return an answer for an item that does not exist, and I would worry about that now.
I'm sure Mike said well that will never happen because we have data validation here but of course anyone can easily override the data validation by putting a value in another cell using copy and then pasting on top of the validated cell, the INDEX and MATCH is going to return NA, the LOOKUP though is going to happily return that value so watch out for that also a cool little trick there you have spreadsheets where someone has used data validation you now know how to very easily over ride that; not that I'm trying to get you to break anyone’s spreadsheets, but sometimes you know just a little too harsh on you there and you really have a new item that you really have to get in that list.
Hey I want to thank everyone for stopping by; I want to thank Mike for being a part of these dueling Excel podcasts, on behalf of Mike and myself; see you next time for another dueling Excel.
I'm Bill Jelen. Now, today we're going to talk about VLOOKUP, but a really weird situation with VLOOKUP; VLOOKUP of course I think is the most powerful function in all of Excel.
We can use VLOOKUP to go grab for example a description based on SKU, so here we have a drop down where they choose and SKU and then this VLOOKUP is grabbing the description from this table over here in L and M, now as you know VLOOKUP requires the key field to be in the leftmost column and then the information trying to get to the right of that, but sometimes the data set up another way and here to get this price the prices to the left of SKU, now the easiest solution of course just move the price to the right but sometimes you don't have control over this data it might be someone else's data and it's set up wrong, how can we do that?
Well I'm going to show how to use two functions to replace the VLOOKUP, boy wouldn't it be great note to Microsoft in case you're watching to be able to say that we wanted the negative one column instead of saying we want the second column state -1, problem solve life would be good unfortunately it doesn't work that way so we have to use two other functions, in these functions; I love this solution because both of these functions seem completely useless you would never use either one of them.
The first function is called MATCH and if you understand VLOOKUP you understand MATCH say MATCH BR-15-3 in this column of numbers, Comma False or Comma 0, just like VLOOKUP and that will find the exact match but what it does when it finds it, it tells you what row it’s on and not even the real row number the relative row number, so for example BR15-3 is right here well that is the 1, 2, 3, 4, 5, 6, 7th item in that range of cells, I mean what good is that when would anyone ever know what item number a particular value is found it's just insane I originally read about MATCH; I'm like I'll never ever use this but we're going to use that match result with another function called INDEX, INDEX says hey we have a bunch of prices over here and oh by the way we want the value that occurs at a certain row within that range and optionally a certain column now we don't need to specify the column here because there's only one column so check this out; we start out Equal INDEX of the prices in K2 to K29 and then the INDEX function says well which road do you want within that well I don't know what row I want, but I'm going to use another function called MATCH, go grab C2, figure out where C2 occurs in L2 to L29 and we want an exact match, Comma FALSE and that will return the result, so BR15-3 sure enough 379 dollars let's try it, let's do something else BG33-8 is 125 dollars, there you have it it's working cool thing about MATCH . Now with VLOOKUP, either but Comma TRUE or Comma FALSE, the Comma 0 in the MATCH says we want an exact match there's two other options though, you can put a 1 or a -1 and that says hey if the data is sorted then I want the value just below it or just above it in case there's not an exact match, so a little bit more things you can do with MATCH although to be honest every time I do it I put Comma FALSE or Comma 0 because I'm always looking for the exact match.
Now this is the method I use all the time let's see what Mike has, we'll throw it over to Mike.
Mike Grivin (03:45): Thanks MrExcel, hey I like this formula right here and I'll just show you right off the bat why MrExcel is going to get the point right here this is MATCH, so I’m going to delete this and then type a comma there's the drop down in 2007 you can do exact MATCH; one which is less than which would be just like VLOOKUP and then MATCH has this other option as MrExcel.
I'll mention -1 that does greater than, so total versatility with the INDEX and MATCH, ah so INDEX MATCH, Exact Match, let's go ahead and what I'm going to do is use the LOOKUP function, equals LOOKUP , we still have to look up our value, Comma, but look at this unlike the VLOOKUP which has the lookup column and then the retrieval column next to each other you have two arguments here in the LOOKUP; these arguments the LOOKUP vector we're going to say is SKU here and Comma, then we have a result vector the great thing about both of these arguments is that they can be orientated anyway, so right now we have SKU and then to the left we have our retrieval column, but they could be one horizontal, one vertical, they could be any setup any orientation I'm going to close parentheses and then ENTER, now let's just go ahead and test this; click the drop-down oh that's looking great that's looking, oh now there's a problem here and INDEX and MATCH got it right but LOOKUP didn't, the problem is LOOKUP only does approximate match and if this is not sorted in ascending order, now we all know from LOOKUP what ascending order means; you know when we're doing numbers we have smallest to biggest but this is, these are words, these are letters, so the solution if you're allowed to sort the column then you can use LOOKUP, in 2007 I'm simply going to well there's field names at the top and rows and records, we have our little database in essence, in 2007 you can just right-click the field and sort, sort and I'm going to say A to Z, now all the Bs, all the Cs, etc in alphabetical order, now LOOKUP and this INDEX MATCH set up will return the same value every single time, so LOOKUP, great you only have one function if you can sort your LOOKUP column otherwise, INDEX MATCH much more versatile function because of that lost argument.
All right there's a point for MrExcel, all right see you next trip.
Bill Jelen (06:35): Mike that is brilliant, you know I love these dueling Excel podcast because you come up with completely different ways of attacking things, I always thought that LOOKUP was just something left over for a long, long time ago.
But, the fact that you can have a LOOKUP vector and a result vector and I want to put a challenge out there to the people watching, does anyone have a real life example where you have a LOOKUP vector that's vertical and a result vector that's horizontal?
I'm trying to figure out a great example where I could use to show that in my seminars that would be so cool, now the one other thing I need to caution you about is that LOOKUP because it's doing an approximate match is going to return an answer for an item that does not exist, and I would worry about that now.
I'm sure Mike said well that will never happen because we have data validation here but of course anyone can easily override the data validation by putting a value in another cell using copy and then pasting on top of the validated cell, the INDEX and MATCH is going to return NA, the LOOKUP though is going to happily return that value so watch out for that also a cool little trick there you have spreadsheets where someone has used data validation you now know how to very easily over ride that; not that I'm trying to get you to break anyone’s spreadsheets, but sometimes you know just a little too harsh on you there and you really have a new item that you really have to get in that list.
Hey I want to thank everyone for stopping by; I want to thank Mike for being a part of these dueling Excel podcasts, on behalf of Mike and myself; see you next time for another dueling Excel.
