The arguments of INDEX function is
=INDEX(array,row_num,[column_num])
Array refers to the range where the output resides.
Row_num refers to the relative position of row.
[Column_num] refers to relative position of column. Square bracket means optional argument. in our case, array is single dimension and so need of column num, so we can ignore that argument.
Let me give you the basic example in this post and for damn sure will get you along with me for deep dive in Index in further posts.
Example #1:
Range D4:D15 contains Month wise sales, In G5 cell we need to extract the Mar month sales.
Our output must be dynamic, so if I change the month into Jun, then it should pick the sales from D9 cell.
So, in this case, our result lies some where in the range D4:D15 and that’s our array for the index function. To make the row number argument dynamic, I am going to get the help from match function because it gives the relative position as the output.
What the match function does here is, it matches the Mar month in the range C4:C15 and gets the relative position of Mar month, in our case it will provide the out put as 3.
Just to evaluate each argument, please follow the below steps to see how the formula calculates or evaluates.
Click inside the formula, in the place where you want to check the output, then in the formula intellisence click the argument as shown below.
Since I clicked row_num in the argument, entire match function is selected as shown below.
Then I pressed F9 key, which is the short cut key to evaluate the selected argument. The result of this evaluation is 3.Then the index function gives the third position value in the range of D4:D15 which is the sales amount 118.
Example #2:
What if the data is across columns like below pic.
Here, since the data is across columns I should have used match function in column num argument to get the relative position of Jun month column. The range we used in array argument is one dimensional array of size 1 row, 12 column. So, index function programmed to understand that output of row num argument will be used either for row or column based on the range.
Comentários