Using INDEX to Extract a Value from an Excel Array Formula's Result - dummies

Using INDEX to Extract a Value from an Excel Array Formula’s Result

By Conrad Carlberg

Not all array formulas return arrays with multiple columns and/or multiple rows to the worksheet. But when they do, it can happen that you’re interested in seeing only one value in the array. You can use Excel’s INDEX function to help with that.

For example, LINEST is one of the worksheet functions that will work properly only if you array-enter the formula that contains the function. But suppose that you want access to only one cell value in the LINEST results, perhaps to accommodate a worksheet layout in a routine report. In that case, you don’t necessarily want the full set of LINEST results, and you can use Excel’s INDEX function to pluck out and display only the one you’re interested in showing.

For example, here’s how you might array-enter LINEST for a multiple regression:

=LINEST(A2:A51,B2:D51,,TRUE)

If you array-enter that formula in a 5 row by 4 column range, the intersection of that range’s third row and first column contains the regression’s R-squared value. So if you select just a single cell and enter the following formula, you’ll get the R-squared value only:

=INDEX(LINEST(A2:A51,B2:D51,,TRUE),3,1)

Here, you’re supplying INDEX with the array of values returned by the LINEST function. That’s the first argument to INDEX. The second and third arguments to INDEX are the numbers 3 and 1, which instruct INDEX to find the value in the third row and first column of the array and return it to the worksheet.

You can enter the full INDEX formula as just given normally, with an array of LINEST results as its first argument, without the Ctrl and Shift and Enter combination — that is, without array-entering it. (Try it both ways, both array entering it and entering it normally.)

And yet if you try entering the following single-cell array formula, it produces the error #VALUE! if you try to enter it normally:

=IF(H44639:H44644>0,G44639:G44644,0)

When the formula calls a function that Excel expects to take an array as an argument, the formula can be entered normally. That’s the case with this formula:

=INDEX(LINEST(A2:A51,B2:D51,,TRUE),3,1)

The LINEST results are nested within the INDEX function, where they act as its first argument. Excel expects INDEX to take an array of values as its first argument — parsing an array is what INDEX was born to do. So the formula as given does not need to be array-entered.

In contrast, this single cell array formula must be array-entered:

=AVERAGE(IF(A2:A25="Zig",B2:B25,""))

In this case, Excel does not expect that the IF function will take an array of values as an argument, but here we’re presenting not one but two arrays of values to IF: the range A23:A25 and B2:B25. (You can even take the position that there’s an array of 24 instances of “” implied by the first two arguments.) Because the formula does not meet Excel’s initial expectation of the arguments to IF, you have to draw Excel’s attention to the situation, and you do so by array-entering the formula.