INDEX
Fetches value at the intersection point of specific column and row
Syntax of Excel INDEX formula:
Array form:
=INDEX(array,row_num,[column_num])
Reference form:
=INDEX(reference,row_num,[column_num],[area_num])
In words:
Array form:
=Get the value (by looking up in this range of cells that is at the intersecting point of, this row, and this column)
Reference form:
=Get the value(by looking in these references lying at the intersection of, this row, [this column], [and this reference])
Explanation
Excel’s INDEX function has two forms. Which form you are using depends on the input and the desired output of the formula. Array formulas are different from simple ones because array formulas needs to be entered using CTRL+SHIFT+ENTER combination.
In simple words if you want to refer to specific cell or have multiple ranges to lookup we use reference form whereas if we are after a specific cell or array of cells we use array form.
Examples of INDEX Formula
Following data will be used for the examples below:
Finding one value from one range
To fetch value of units of department 1 the formula will be:
=INDEX(A3:D6,3,2) and it will return 25.
Where A3:D6 is a range containing data of products. As product CC is in third row of this specific range and units are in second column therefore we used 3 and 2 as arguments for row_num and column_num respectively.
Fetching range of cells
Index function can be used to fetch a range of cells instead of just single value. For example if you want to calculate the average of price/unit of department 1 then formula to be used is:
=AVERAGE(INDEX(A3:D6,0,3))
In the above formula we have a range A3:D6 where lookup will be performed. For row_num we have zero because are not after a single value but all the rows of column price/unit which is third therefore column_num argument has 3 as value. Index function will fetch all 4 values and AVERAGE function will then perform the average of all 4 values.
Fetching a reference or range of cells from multiple ranges
In the above examples I restricted only to Department 1. And if I have to making the calculations relating to other departments I will have to change the formula or a part of formula. However, I can manage it by mentioning all the ranges at once and then make the calculations easier using area_num argument. Where area_num argument is a referred to cell where we will mention the area number to update the records easily.
If area_num is mentioned in cell B16 then following formula is to be used:
=AVERAGE(INDEX((A3:D6,F3:I6,A10:D13,F10:I13),0,3,B16))
Following animation shows how this works:
Reproduce the whole array
If you want the whole array to be extracted then you have to do two things:
- Pre-select matching number of cells
- Enter the formula as array formula using CTRL+SHIFT+ENTER
Suppose we want to extract the units column of Department 2 for this we will use the following formula by first selecting the cells and then hitting the CTRL+SHIFT+ENTER combination.
