Is there any documentation of the behavior of built-in Excel functions called with array arguments?

Question

(I originally asked this on stackoverflow. However, Excel formulas definitely straddle the line between "programming" and, um, "superusing", so I believe this cross-post is appropriate.)

Many of Excel's built-in functions can take array arguments. Sometimes the result is documented in the help and sometimes not. So:

=IF({1,0,1}, 42, 99)

will return {42, 99, 42}. The help for 'IF' covers array arguments. But:

=INDEX({2,3,5,7,11}, {2,4})

will return {3, 7}. This is intuitive, but I can't find a Microsoft source that documents it. And:

=INDEX({1,2,3,4;5,6,7,8;9,10,11,12;13,14,15,16}, {1,3},{2,4})

returns {2, 12}, which is not intuitive at all.

Is there any source that covers these less-common array usages? It seems like there has to be one, but I'm not finding it in a web search because it requires using "Excel", "function", and "array"...

(Also, can someone tag this question with 'array-formula' since I lack the power to create tags?)

I'm comfortable with using array formulas and use them all the time, but I hate having to figure out what will happen by trial and error. This particular question arose when I was doing the (simplified equivalent of):

=IF(ISNA(udf()), {1,2,3,4}, {5,6,7,8})

where 'udf' was returning, say, {1,2,3}, and the formula was evaluating to {5,6,7,#N/A}. This surprised me, even though I can figure out what is going on and work around it. It just bugs me that I can't find an authoritative source that lays it all out.

Link to question on stackoverflow (there are several answers and some comments, but nothing that satisfies the question right now) here: https://stackoverflow.com/questions/3998632/is-there-any-documentation-of-the-behavior-of-built-in-excel-functions-called-wit

EDIT: I made a large edit in the stackoverflow version of this question, and incorporated part of Neal's answer there. The addition is very much on the "programming" side of Excel formulas, though, so I'm not going to repeat it here.

Answer 1

I don't know of anything useful to go with what you are doing either: I can't remember seeing an example of using array formulae (at least with an array where one would expect to see a single thing. You will see arrays used in examples of the INDEX function).

I think that it is relatively simple if you think of what would happen if you expanded the array (Is there a better terminology for this. Please edit if you can think of one). Where you use an array argument where the function would expect a normal argument, the result is an array with the same number of elements as the array argument, each element of the resulting array being what the result would be with the single argument.

So in your first example

=IF({1,0,1}, 42, 99)

you get an array

{ IF(1, 42, 99), IF(0, 42, 99), IF(1, 42, 99)}

which comes out as

{ 42, 99, 42 }

In your last example

=IF(ISNA({1,2,3}), {1,2,3,4}, {5,6,7,8})

the result is

{ IF(ISNA(1),1,5) , IF(ISNA(2),2,6) , IF(ISNA(3),3,7) ,IF(ISNA(),4,8) }

The first three ISNAs return false and the fourth fails, so the result is what you got. I think the only confusing thing here could be where the error value is.

I thought at first that a result of an error would be more logical than an array of three proper elememts and an element that is an error , but, thinking about it, you can enter an array formula into a worksheet range to match the number of elements in the array. If you think of doing this, the error element makes complete sense and may help you find the error.

Answer 2

Charles Williams posted a link to his Decision Models website that has the information I was looking for in his answer on stackoverflow:

https://stackoverflow.com/questions/3998632/is-there-any-documentation-of-the-behavior-of-built-in-excel-functions-called-wit/4003413#4003413

The link itself is:

http://www.decisionmodels.com/optspeedj.htm

(The whole website is an excellent Excel resource, and his FastExcel product is very good, BTW.)