It happens sometimes when you want to get a value from a list and you know the position of that value from the list. Excel INDEX Function can help you to do that.
By using INDEX Function you can fetch a value from a range of cells in which you know the exact position of the value.
In the below example, we have a month wise quantity. And, we have used index function to get quantity for the month of Jun as in the list Jun is on 6th.
Excel INDEX Function has two different syntaxes.
Array – You can use array form of index to simply get a value from a list using its position. INDEX(array, row_num, [column_num])
Reference – Reference form is less used in real life but you can use it if you have more than one range to get value from (I’ll show further, an example to make it understand). INDEX(reference, row_num, [column_num], [area_num])
Quick Tip: If you use INDEX Function as a range reference [A1:Index(B1:B5,1,1)] it will return cell reference instead of value.
Here I have listed some easy to follow examples for both of the forms of INDEX function.
In the below example, I have used INDEX Function to get the quantity of Jun month. In the list, Jun is on 6th position (6th row) that’s why I have specified 6 in row_number.
And, I’m only referring to a single column that’s why I have specified 1 in column_number.
INDEX has returned 1904 in the result.
And, if you want, to get any other value, all you have to do, just mention the position of the value in the list. Whereas, if you referring to a range with more than one column you have to specify the column number.
INDEX will return the value from the intersection of row_number and column_number.
In below example, instead of selecting all the range in one go, I have selected it as three different ranges.
In the last argument, I have specified 2 in area_number which will define the range to use from these three different ranges.
Now, in the second range, I’m referring to 5th row & 1st column. INDEX has returned the value 172 which in the 5th row in the 2nd range.
You can also refer to ranges with multiple columns.
To learn more about Excel INDEX Function you can check Microsoft’s Help Section. And, if you have a unique idea to use it, I would love to hear from you.