When reviewing an Excel spreadsheet it is important to understand the typical errors people make. Below are ideas on easy ways of finding the INDEX errors, in order of riskiness, when auditing an Excel Spreadsheet.
It is useful to have an spreadsheet error add in to find all the risky formulas but if you don’t have one you can do a manual search using the FIND tool.
The Array part of INDEX has no $ signs
The INDEX function will generally refer to an array (range of cells) which doesn’t change. So if an INDEX function is copied anywhere, it should generally have $ signs around the array.
As shown below using the ExcelAnalyzer Formula report tool, focusing on all the INDEX functions in the spreadsheet, the first few have $ signs on the array, but the 3rd one does not, and if you look adjacent you will see there are 13 of them which means there has been a copy/ paste. This looks risky and should be investigated.
If you follow the hyperlink on the 13 to the relevant cells you can use the Trace Formulas to show where all these cells are looking.
As shown below, the first one seems to look at the correct row (row 10), but, because there are no $ signs (absolute reference) the rest of the formula are looking at blank rows. This is definitely a spreadsheet error. Another important lesson here is that you mustn’t just look at the first cell. In fact don’t look at the first cell in a range. It will normally be correct (as this one is). It is only when you look at the copies that the error becomes obvious.