“I’m not a very experienced Excel user. Why does = INDEX(…) sometimes work and sometimes not…. :-)?” — Suzan G.
Probably the best way to answer this question is to explain how to debug your INDEX formulas.
The INDEX function can use either of two sets of arguments:
=INDEX(array, row_num, column_num)
=INDEX(reference, row_num, column_num, area_num)
Usually, we use the second set. And usually, there’s only one area, which allows us to ignore the area_num argument. Most of the time, that is, the function is used like this:
=INDEX(reference, row_num, column_num)
INDEX often is an easier function to debug than most other functions. This is because…
- INDEX returns a reference, which you can test.
- INDEX’s first argument must be a reference, which you can test.
- INDEX’s next two arguments are numbers, which you can test.
So let’s take a look at how to debug the references and numbers.
Testing Formula References
An Excel reference is like a hyperlink to a range in an Excel worksheet. If you have a reference in a formula, the easiest way I’ve found to test it is to tell Excel to go to the range that the formula is referencing.
Suppose you have a formula that includes: INDEX(whatever) If you want to see where the INDEX is referencing, here’s what you do:
- Select the INDEX(whatever) piece of the formula in your formula bar.
- Press Ctrl+C to copy that piece to your clipboard.
- Press ESC to return to the Ready mode.
- Press F5 to launch the Goto dialog.
- Press Ctrl+V to paste the copied text into the Reference edit box in the Goto dialog
- Press Enter to go to that reference.
After you successfully test the reference, you can press F5 then Enter to return to the original location in your workbook.
If Excel gives you an error when you try to go to INDEX(whatever), you know that your INDEX isn’t returning a good reference. There are several likely reasons for this.
One reason could be that the reference is to a workbook that isn’t open. When you have this problem, you’ll usually see a path name within the INDEX function, something like this:
=INDEX(‘C:\Reports\ActGL.xls’!DataMonth,$B11,E$10)
Choose Edit, Links. In the Edit Links dialog, select all workbooks listed. (Select the first one; hold down your Shift key; then select the last one.) Then choose Open Source, which opens all the workbooks you’ve selected.
If you still have an error, use the six-step method about to test the reference in the first argument of your INDEX function. If this reference is broken, you’ve found your problem.
If the first argument’s reference is working, you need to test the row and column numbers.
Testing Row and Column Numbers
Generally, testing these numbers is easy. In the INDEX formula select one of the arguments that is supposed to return a number. Then press F9 to find the value returned.
To illustrate, here’s a formula from a project I’m working on this week:
=INDEX(MonthData, $D12, H$10)
To test that $D12 is referring to a number, select that reference within the formula in the formula bar, then press F9. If cell D12 contains a number, Excel will display that number in the formula bar. Then you can test cell H10 the same way.
At this point, your formula bar will look something like this:
=INDEX(MonthData, 23, 19)
Do not press Enter. If you do, you’ll replace the formulas with hard-coded values as shown. Instead, select “INDEX(MonthData, 23, 19)” then follow the six-step method above, beginning with step 2.
It’s possible that the second or third argument in your INDEX function will be more complex than the simple formula shown above. In fact, it often will contain a MATCH function. You can test that function similarly. Within your INDEX formula select MATCH(whatever) and press F9. If you get an error, you know where your problem lies. Press Esc to return to the Ready mode. Then test each of the arguments in the MATCH function to find the source of the error.
Eventually, you’ll find the problems in your formula and INDEX(whatever) will return a reference. If it’s the correct reference, then you’re done. If it’s not the correct reference, you’ve still made progress. By comparing the INDEX function’s actual selection to the selection you expected, you should be able figure out which of your INDEX’s three arguments is returning an incorrect value.
