Mel sends in today's question. She prefers VLOOKUP, but her husband prefers INDEX and MATCH instead of VLOOKUP. In Episode 697, we'll take a look at when to switch from VLOOKUP to INDEX and MATCH.
This blog 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 the 377 tips from the book!
This blog 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 the 377 tips from the book!
Transcript of the video:
Hey welcome back to the MrExcel netcast.
I'm Bill Jelen.
Now, today we have a question sent in by Mel.
Mel is an accountant and her husband is also an accountant and they're having a disagreement over whether it's better to use VLOOKUP or INDEX, MATCH.
Mel says VLOOKUP works great and but her husband says no you should always use INDEX and MATCH and I'm going to come down on one side here and I'm sure that you might have a different opinion and feel free to write to me bill@mrexcel.com on this argument.
And my basic feel for this is VLOOKUP is an expensive function, it requires a lot of processing to go find A526 through a table of you know thousands of accounts.
Especially, since the table doesn't need to be sorted.
So, Excel might have to search through a couple thousand rows to find the A526 here for January and then when we get to column C and it needs to do the exact same thing for February.
It's going to take just as much time to go find A526.
I mean the fact that we've already found it in cell B2, doesn't mean that it's any faster to find it in C2 and it's going to have to find it 12 times as we go across the worksheet.
For that reason when I have many columns that are looking up the same value I'll tend to switch over to INDEX and MATCH let's take a quick look at how to do that we're going to insert a new column here in column B.
I'm going to call it the Match column and instead of using VLOOKUP.
We use =MATCH(, go find A526 within the first column of our lookup table, press F4 there and 0 or false is the third argument and basically what this does is it tells me that A526 is on row 426 within the relative range, I'll copy that down.
Now, when I first saw this I said what row isn't on, why do I care what row it's on.
I've never had a manager call me up and say hey Bill, what row is that on and so, I initially thought well I'll never have a use for the MATCH command, But we can now, use the INDEX function.
The INDEX function will say hey go look through the second column of our table.
Now, I want to make this reference be absolute but only for the rows.
So, I'm going to press F4 two times to freeze only the one through 1053 and then we need to say which row do we wanna grab.
Well, we wanna grab the row that's in B2 and I want that to freeze only the column.
So, I'm going to press F4 , 3 times.
I don't have to specify the column, since I have a one column range and very quickly we get the answer with INDEX.
Now, copy this down to all of my rows and then copy it across to all of the months and you'll see that I have the exact same results that I had with VLOOKUP.
Now, it does require a few extra calculations here there's one column, but my argument is that we only have the expensive MATCH function or VLOOKUP function occurring in one column and all the other columns are doing a lightning fast INDEX function.
So, what we're going to do here is spend some time, theoretical time I might realize we're talking about nanosecond spend some time in column B finding the row numbers, but then in column C through N it's going to be incredibly fast.
For that reason especially, if I have thousands of rows I'm looking up, and I'm possibly looking up into 10's or 50,000 rows of data.
You will notice an improvement using INDEX and MATCH over VLOOKUP.
You might of course wanna hide this column, I'll right-click and choose Hide and so, that way it still appears that we have our account numbers and all of the monthly values.
So, that's my tape I use VLOOKUP all the time until I get to the point where I have a huge number of columns, and I'm worried about response time then I'll switch over to use one column that has the match and then another column that makes use of that match in the index.
Oh hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Now, today we have a question sent in by Mel.
Mel is an accountant and her husband is also an accountant and they're having a disagreement over whether it's better to use VLOOKUP or INDEX, MATCH.
Mel says VLOOKUP works great and but her husband says no you should always use INDEX and MATCH and I'm going to come down on one side here and I'm sure that you might have a different opinion and feel free to write to me bill@mrexcel.com on this argument.
And my basic feel for this is VLOOKUP is an expensive function, it requires a lot of processing to go find A526 through a table of you know thousands of accounts.
Especially, since the table doesn't need to be sorted.
So, Excel might have to search through a couple thousand rows to find the A526 here for January and then when we get to column C and it needs to do the exact same thing for February.
It's going to take just as much time to go find A526.
I mean the fact that we've already found it in cell B2, doesn't mean that it's any faster to find it in C2 and it's going to have to find it 12 times as we go across the worksheet.
For that reason when I have many columns that are looking up the same value I'll tend to switch over to INDEX and MATCH let's take a quick look at how to do that we're going to insert a new column here in column B.
I'm going to call it the Match column and instead of using VLOOKUP.
We use =MATCH(, go find A526 within the first column of our lookup table, press F4 there and 0 or false is the third argument and basically what this does is it tells me that A526 is on row 426 within the relative range, I'll copy that down.
Now, when I first saw this I said what row isn't on, why do I care what row it's on.
I've never had a manager call me up and say hey Bill, what row is that on and so, I initially thought well I'll never have a use for the MATCH command, But we can now, use the INDEX function.
The INDEX function will say hey go look through the second column of our table.
Now, I want to make this reference be absolute but only for the rows.
So, I'm going to press F4 two times to freeze only the one through 1053 and then we need to say which row do we wanna grab.
Well, we wanna grab the row that's in B2 and I want that to freeze only the column.
So, I'm going to press F4 , 3 times.
I don't have to specify the column, since I have a one column range and very quickly we get the answer with INDEX.
Now, copy this down to all of my rows and then copy it across to all of the months and you'll see that I have the exact same results that I had with VLOOKUP.
Now, it does require a few extra calculations here there's one column, but my argument is that we only have the expensive MATCH function or VLOOKUP function occurring in one column and all the other columns are doing a lightning fast INDEX function.
So, what we're going to do here is spend some time, theoretical time I might realize we're talking about nanosecond spend some time in column B finding the row numbers, but then in column C through N it's going to be incredibly fast.
For that reason especially, if I have thousands of rows I'm looking up, and I'm possibly looking up into 10's or 50,000 rows of data.
You will notice an improvement using INDEX and MATCH over VLOOKUP.
You might of course wanna hide this column, I'll right-click and choose Hide and so, that way it still appears that we have our account numbers and all of the monthly values.
So, that's my tape I use VLOOKUP all the time until I get to the point where I have a huge number of columns, and I'm worried about response time then I'll switch over to use one column that has the match and then another column that makes use of that match in the index.
Oh hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
