Excel: Can I use INDEX with an ARRAY to return multiple values?

I was trying to grab a range of data using an array as an input to an INDEX (the array in the real formula comes from a MATCH). But the formula I came up with only returns the first hit (Cell A1): answer 1 rather than the sum of rows 1 and 2 as I intended. This is regardless of whether I ENTER or CSE the formula, or even wrap the INDEX in "N()". Is there a way I can grab multiple cells in one INDEX function? I was trying to avoid volatile functions since I have tens of thousands of data points in several tables. =SUMPRODUCT(INDEX(A1:C4,{1,2},1)) Data rows 1-4 of columns A-C:

1 1 1
2 2 2
3 3 3
4 4 4

This question generated 20 answers. To proceed to the answers, click here.

This thread is current as of March 13, 2014.

For more resources for Microsoft Excel