Return An Array Using in Excel Using INDEX Function

For any reason, if you want to do some operation on a range with particular indexes, you will think of using INDEX function for getting array of values on specific indexes. Like this.

Index with INDEX

Let's Understand it With an Example.

Here, I have a Rate column. I want to sum values at position 1,3, and 5. I write this formula in C2.

=SUM(INDEX(A2:A11,{1,3,5}))

Surprisingly, INDEX function does not returns an array of values when we supply an array as indexes. We only get the first value. And SUM function returns the sum of one value only.
0081
So how do we get INDEX function return that array. Well I found this solution on internet and I don’t really understand how it works, but it works.

The Generic Formula to get Array of Indexes

Range: It is the range in which you want to look for at given index numbers
Number: these are the index numbers.
Now let’s apply above formula to our example.

Write this formula in C2.

=SUM(INDEX(A2:A11,N(IF(1,{1,3,5})))

And this returns the correct answer as 90.
0082
How It Works.

Well, as I said that I don’t know why it works and returns the array of numbers but here what happens.

IF(1,{1,3,5}): This part returns the array {1,3, 5} as expected

N(IF(1,{1,3,5})) this translates to N({1,3,5}) which again gives us {1,3,5}

NOTE: The N function returns the number equivalent of any value. If a value is not number convertable it returns 0 (like text). And for errors it returns error.

INDEX((A2:A11,N(IF(1,{1,3,5}))): this part translates to INDEX((A2:A11,{1,3,5}) and somehow this returns the array {10,30,50}. As you have seen earlier when write INDEX((A2:A11,{1,3,5}) directly in the formula it only returns 10. But when we use N and IF function it returns the whole array. If you understand it let me know in the comments section below.

How to use cell references for index values to get an array
In the above example, we hardcoded the indexes in function. But what if I want to have it from a range that may change. We replace {1,3,5} with range? Let’s try it.
0083
Here we have this formula in Cell D2:

=SUM(INDEX(A2:A11,N(IF(1,A2:A5)))

This returns 10. The very first value of the given index. Even if we enter it as an array formula using CTRL+SHIFT+ENTER, it gives the same result.

To make it work add + or -- (double unary) operator before range and enter it as array formula.

{=SUM(INDEX(A2:A11,N(IF(1,+A2:A5)))}

Index with INDEX

This works. Don’t ask me how? It just works. If you can tell me how I will include your name and explanation here.
So we learned how to get an array from INDEX function. There’s some crazy stuff going on here in excel. If you can explain how it works here in the comments section below, I will include it in my explanation with your name.
 
Download file:

 
Related Articles:

Arrays in Excel Formula

Use INDEX and MATCH to Lookup Value

How to Use LOOKUP function in Excel

Lookup Value with Multiple Criteria

Popular Articles:

The VLOOKUP Function in Excel

COUNTIF in Excel 2016

How to Use SUMIF Function in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube