These two functions allow you to dynamically point to a cell or a range. While they are often useless on their own, they can be used as arguments to another function. Episode 485 introduces these two 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!
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!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Now, today we're going to talk about two functions that are going to seem pretty much useless until we bring it home tomorrow.
I'm going to talk about the INDEX function and the OFFSET function.
The INDEX function is when you have some range of data.
So, let's say I'm interested in this range here from C1 to out to E17, and I want a function that will return the value at a particular row and a particular column.
So, the INDEX function says take the index of C1 to E17 give me the data that's in the row number B2 and the data that's in the column number at B3.
Now, I set up a conditional format here, so we can actually see this.
So, if we ask for row 1 column 1, we're just going to get the one from C1.
But if we ask for let's say row 7, column 3 then we're going to get the 41 that's out at the end and when I first saw this I have to admit I had no idea what it would be useful for.
But tomorrow you'll see how we can use this with the MATCH function.
But while we're talking about INDEX let me talk about the kind of related function, it's called the OFFSET function.
Now, the offset function has five arguments, here with the OFFSET function you start from a single cell.
So, I'm going to start from the green cell in C2, and we say that we want to go a certain number of rows down, a certain number of columns right and then return a range that is a certain height and a certain width.
So, here I have that function set up again with a conditional format.
So, we can see it if I say to start from C2, that's the green cell and go one row down, zero columns right, return a height and width of one, than I'm gonna get this yellow cell.
Now, this is very flexible let's say that I wanted to go two columns over and ask for something that was four rows high.
Now, I'll be able to point to that specific range.
So, there's times when you need to do some conditional summing or something like that, where you'll want to use the OFFSET function and since the OFFSET function can return more than one cell.
You'll usually wanna use that in conjunction with a sum or an average.
Two very interesting functions INDEX and OFFSET.
Now, yesterday we talked about VLOOKUP tomorrow I'll show you how to use the INDEX function combined with MATCHto replace VLOOKUP sometimes, doing something that is much much faster than whole series of VLOOKUPS.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Now, today we're going to talk about two functions that are going to seem pretty much useless until we bring it home tomorrow.
I'm going to talk about the INDEX function and the OFFSET function.
The INDEX function is when you have some range of data.
So, let's say I'm interested in this range here from C1 to out to E17, and I want a function that will return the value at a particular row and a particular column.
So, the INDEX function says take the index of C1 to E17 give me the data that's in the row number B2 and the data that's in the column number at B3.
Now, I set up a conditional format here, so we can actually see this.
So, if we ask for row 1 column 1, we're just going to get the one from C1.
But if we ask for let's say row 7, column 3 then we're going to get the 41 that's out at the end and when I first saw this I have to admit I had no idea what it would be useful for.
But tomorrow you'll see how we can use this with the MATCH function.
But while we're talking about INDEX let me talk about the kind of related function, it's called the OFFSET function.
Now, the offset function has five arguments, here with the OFFSET function you start from a single cell.
So, I'm going to start from the green cell in C2, and we say that we want to go a certain number of rows down, a certain number of columns right and then return a range that is a certain height and a certain width.
So, here I have that function set up again with a conditional format.
So, we can see it if I say to start from C2, that's the green cell and go one row down, zero columns right, return a height and width of one, than I'm gonna get this yellow cell.
Now, this is very flexible let's say that I wanted to go two columns over and ask for something that was four rows high.
Now, I'll be able to point to that specific range.
So, there's times when you need to do some conditional summing or something like that, where you'll want to use the OFFSET function and since the OFFSET function can return more than one cell.
You'll usually wanna use that in conjunction with a sum or an average.
Two very interesting functions INDEX and OFFSET.
Now, yesterday we talked about VLOOKUP tomorrow I'll show you how to use the INDEX function combined with MATCHto replace VLOOKUP sometimes, doing something that is much much faster than whole series of VLOOKUPS.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
