The Index function for finding a value buried in an avalanche of data in Excel
The INDEX function is very useful for finding a specific value buried in thousands of data points.
Used in conjunction with the MATCH function, it makes the job of finding information quick and effective.
The INDEX function is particularly useful when you have information organised in an array as in the following picture with potentially hundreds or thousands of rows and many columns.
INDEX Function in Excel – Starting Point
The first step is to identify both the column and the row of the value we want to extract from the array.
We do this by using the MATCH function in order to help Excel to understand which column is should be considering.
Index function in Excel – using MATCH in conjunction
In this particular case our input ‘February’ relates to the second value in the array of Months.
Similarly, our Product input of ‘A4′ relates to the fourth value in the array of products.
We have therefore told Excel that we are interested in extracting from the Sales array the value in Column 2 and in Row 4.
We then use INDEX to extract the value by using the syntax ‘=INDEX(array,row_num,column_num)‘
in our case it becomes ‘=INDEX(E4:P163,B10,B9)’ as our set of data is included between cells E4 and P163 while the row number and the column number have been identified in cells B10 and B9.
Index function in Excel – Final Result
With a few formulas you are able to save yourself hours of time in finding information buried in an avalanche of data!
