Learn Microsoft Excel Training with Free Lessons & Tips

INDEX function is a powerful tool that allows you to retrieve values from a specified range or array based on row and column numbers.

INDEX(array, row_num, [column_num])

As it requires row_num and column_num, it is mostly used with MATCH function (used to calculate the position number of a value in the given range) for more flexibility and dynamic lookups.

It can also be used with other functions like SUM, AVERAGE, MAX, or MIN to perform calculations on specific data points.

For example, if you have to calculate sum of first five values in a given range A1:A10, use can be written as:

=SUM(INDEX(A1:A10, 1):INDEX(A1:A10, 5))

Feel free to contact me if you want to master INDEX function.

Close a workbook. Ctrl+W Open a workbook. Ctrl+O Go to the Home tab. Alt+H Save a workbook. Ctrl+S Copy selection. Ctrl+C Paste selection. Ctrl+V Undo recent action. Ctrl+Z Remove cell contents. Delete Choose a fill color. Alt+H, H Cut selection. Ctrl+X Go to the Insert tab. Alt+N Apply bold formatting. Ctrl+B Center align cell contents. Alt+H, A, C Go to the Page Layout tab. Alt+P Go to the Data tab. Alt+A Go to the View tab. Alt+W Open the context menu. Shift+F10 or Windows Menu key Add borders. Alt+H, B Delete column. Alt+H, D, C Go to the Formula tab. Alt+M Hide the selected rows. Ctrl+9 Hide the selected columns. Ctrl+0