MrExcel's Learn Excel #486 - Match & Index

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on May 1, 2009.
In todays podcast, we have hundreds of rows of VLOOKUP functions. Learn some tricks to make the entry of the third argument easier using a hidden row or the COLUMN function. Ultimately, though, after several iterations of VLOOKUP solutions, Episode 486 finally gets to the point of replacing VLOOKUP with MATCH and INDEX functions.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
maxresdefault.jpg


Transcript of the video:
Hey! Welcome back to the MrExcel podcast.
I'm Bill Jelen.
For the past couple of days, we talked about VLOOKUP and then yesterday we talked about two seemingly useless functions called INDEX and OFFSET.
Now, let's go back to VLOOKUP today, and see where INDEX can come in very, very handy today.
I have a budget process.
We have accounts going down the left-hand side, months going across the top, and for my MIS Department, I'm getting some sort of a download every day that has actuals or budget numbers or something like that's just a real ugly data set and I want to be able to take those numbers from the ugly data set and bring it into my formatted data set every day.
So, I'm gonna use the equal VLOOKUP function.
So, that I want to grab this account number and of course I want to hit the [ F4 ] key three times to make sure that it's always pointing at column A and then I will use [ alt + tab ] to switch over to the other file.
Specify my data area because this is in another workbook, I don't have to hit [ F4 ] It's already selected for me, which column do I want on with the second column and then the word false.
So, that creates the first vlookup.
Now, kind of the hassle is as I copied this over.
It's always going to be looking at the second column.
So, I have to edit each column to change the 2 to A3, the 3 to A4, the four 2 A5.
A great workaround for that is just add maybe a hidden row up here, in row four with the numbers two three four and then in your vlookup instead of saying you want the second column point to C$4 and then as you copy over it will automatically go to columns two three four or five.
Now, we can do that one better if you don't want to add the two, three, four, five in a hidden row, there's a great function called the COLUMN function.
So, here instead of saying I want column 2.
I've said I want the column of B1.
The column of B1 since B is the second column is 2.
The nice thing about that is since the B1 is a relative reference as I copy it over.
It automatically changes to two, three, four, five, six basically.
Now, I have one formula that works everywhere but Vlookup is a very memory-intensive function.
Every time that we do a vlookup in January, it has to go find that account number look through hundreds of rows of data, figure out which row it's in and then it does the exact same thing again for February, March, April, May, June.
That's where match in index can come in very handy.
Here, I've created a brand new column called 'which row' and later on I'll hide this column.
In the 'which row' column uses the MATCH function.
MATCH is very similar to vlookup.
It says hey go grab that account number in column A, look in our worksheet through column A and tell me which row number within the relative range of time.
So, here you'll see that, that's the first account is on row one in the other worksheet.
Now, usually that's not very useful information.
I've never had a manager call me up and say hey, what row number is that account on?
It's just not something that comes up and so when I initially saw the match function I said well what good is that.
Why do I care, what relative row number, within a range something's located.
But if you think about the index function.
We talked about the index function in yesterday's podcast.
The index function says hey go grab a range of data and give me a specific row and optionally a specific column.
So, when I built my index function here.
I said, I'm gonna go over to the other worksheet look through column B and I made sure that the B portion was relative the rows, of course.
I want to keep absolute from dollar sign to 2 dollar sign 363.
But I took the dollar signs out before the B and then said which row do I want?
I want the row that's called out by this MATCH function in column C and since I'm only specify in one column I didn't have to specify which column number.
The great thing about that is the match function is memory-intensive.
But now we only have one match function one for every row.
It's not repeated all the way across the index function is relatively Lightning-fast, compared to the match function.
I have my 12 Index functions, going across and just copy those down to all my rows and it will run much faster than having vlookups through 12 columns times hundreds of rows.
There's one option where index and match makes your spreadsheet work a lot faster because it doesn't have to go through and find the account number twelve times for every row.
Now, next time we'll talk about another option for index and match.
When your data is to the left of the key.
But thanks for stopping by, we'll see you next time for another netcast from MrExcel.
 

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top