Function Index()

Index()


Index function can be used in 2 forms - Array form and the Reference form. I will take each of these here.


Array form: In the first type, it is used to fetch a value from a table / array by making an intersection point using the row number and the column number. In this form, you can specify only a single array from which the values are to be fetched.


Reference form: This form also works similar to the first one, with the only exception being that in this case, you can mention more than 1 arrays / tables from which the value will be looked up and in the function you also mention which reference (i.e. the table / array) should actually the should be fetched from.




Syntax:


Array form: = Index (Array, Row_number, Column_Number)


Array: This argument is the array from which the value should be picked. The array can be a table or a range of cells with single column or row
Row_number: This argument specifies the row number from which the value will be picked
Column_number: This argument completes the function by providing the column number which will be used to create the intersection with row number to fetch the number




Reference form: = Index (Reference, Row_number, Column_number, Reference_number)


Reference: This argument specifies the ranges / tables from where the the value should be fetched using the index number. In case there are more than 1 references mentioned, this argument itself should be enclosed within the parentheses

Row_number: This argument specifies the row number from which the value will be picked

Column_number: This argument completes the function by providing the column number which will be used to create the intersection with row number to fetch the number

Reference_number: This argument informs the function which reference should be considered among multiple references provided 

Examples:

Array form: 

Let us create an example worksheet. In the column A fill cells A1 to A10 with the number from 10 to 100 with an increment of 10. And for the cells B1 to B10, fill them with alphabets A till J.

Now let us suppose you want to fetch the item that is in the 2nd  column and the 3rd row of the table that we just created. For this, the formula should be:

= Index (A1:B10, 3, 2) 

The result will be: C. Similarly if we want to fetch the item in 4th row of the column 2, the formula will be

= Index (A1:B10, 4, 2)

Reference form:

Now let us take another example here. In the same that we just created let us create another table. From the cells A15 to A20 fill the range with values 1 to 6. And in the cells B15 to B20 fill the range with alphabets U to Z. Now let us use one of the previous examples. Let us assume that we want to fetch the item in 3rd row and the 2nd column. The modified formula would be:

= Index ( (A1:B10, A15:B20), 3, 2, ....)

In the above function there are 2 things that I will explain here. One - I have mentioned 2 arrays in this function, from either of which I can pick up the value as referred by the row and the column number. 

Second - In place of reference number, I have used three dots (...) instead of using any value. I have kept it like that only for explanation, else it will give an error. This argument will tell the function from which array out of the 2 provided to the function, the value should be picked up from. The 2 arrays are - (A1:B10, A15:B20).

If the reference number is left blank, the function be default takes the first array and returns the value. Otherwise the results would be as follows;

• = Index ( (A1:B10, A15:B20), 3, 2, 1) – the result would be C
• = Index ( (A1:B10, A15:B20), 3, 2, 2) – the result would be W

So, the reference_number argument is used to identify which table should be used to pick the value from.

Share on Facebook Share on Twitter Share on Google Plus

About Dhakkanz

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.