In our Fast Excel Development Method, we use INDEX extensively because it is a fast and reliable way to join two tables of data. When the value in D3 is 6, the formula returns “Watermelons”.

The formula =INDEX(B2:B11,D3) returns the item in that cell defined by the row number given in D3. Column_num is the column number, not required if you have a range that is one column wide.įor example, here is a list of fruit in the range of cells B2:B11. row_num is a number that refers to the row number of that range. In this context, “array” is a range of cells. If you feed the formula a range and a row (and column) number then the formula will give you the value that sits in the cell. INDEX returns the value of a cell that sits in a range, given the row and/or column number. Want to know why? Seen “#SPILL!” in a cell and wondered what it means? Then read on because this is a story of INDEX and dynamic arrays. Do this and you will be fine – Your Fast Excel Workbooks will run fast and clean. The main take-away from this: If you are on Excel 365 use in place of INDEX.

For people using our Fast Excel Development Method this is an important update that may have substantial effect on the way that your workbooks perform.

In January 2020 and Excel 365, Microsoft made a significant change to the way many formulas behave, including INDEX. There are over 450 different formulas in Microsoft Excel.

Excel 2010 2016 compatibility