Home > Excel
for Business >
Debugging INDEX in Excel
"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.
INDEX 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
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
- 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
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:
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
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
=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.
Hope this helps,