Exceljet

Quick, clean, and to the point

Return array with INDEX function

Excel formula: Return array with INDEX function
Generic formula 
=SUM(INDEX(range,N(IF(1,{1,2,3}))))
Explanation 

To get INDEX to return an array of items to another function, you can use an obscure trick based on the IF and N functions. In the example shown, the formula in E5 is:

=SUM(INDEX(data,N(IF(1,{1,2,3}))))

where "data" is the named range B5:B10.

How this formula works

It is surprisingly tricky to get INDEX to return more than one value to another function. To illustrate, the following formula can be used to return the first three items in the named range "data", when entered as a multi-cell array formula.

{=INDEX(data,{1,2,3})}

The results can be seen in the range D10:F10, which correctly contains 10, 15, and 20.

However, if we wrap the formula in the SUM function:

=SUM(INDEX(data,{1,2,3}))

The final result is 10, while it should be 45, even if entered as an array formula. The problem is that INDEX only returns the first item in the array to the SUM function. To force INDEX to return multiple items to SUM, you can wrap the array constant in the N and IF functions like this:

=SUM(INDEX(data,N(IF(1,{1,2,3}))))

which returns a correct result of 45. Similarly, this formula:

=SUM(INDEX(data,N(IF(1,{1,3,5}))))

correctly returns 60, the sum of 10, 20, and 30.

This obscure technique is sometimes called "dereferencing", because it stops INDEX from handling results as cell references, and subsequently dropping all but the first item in the array. Instead, INDEX delivers a full array values to SUM in. Jeff Weir has a good explanation here on stackoverflow.

Note to readers: I'm not sure exactly why this works. If you can provide a clear explanation, I will update to include.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.