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

Question

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"...

EDIT:

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.

MORE:

With the help of the answers here, and also from the answer to my cross-posting on Superuser (http://superuser.com/questions/203177/is-there-any-documentation-of-the-behavior-of-built-in-excel-functions-called-wit), let me attempt a more precise description of how array formulas "work". The point of my question is to find out if there exists a similarly precise, but official (or at least authoritative), description somewhere that also fills in the missing pieces in this description.

So here goes. (I copied and modified the first part of this from Neal on Superuser):

When you use one or more array arguments in place of scalar argument(s) in a formula, the formula evaluates to an array with the same number of elements as the array argument(s). Each (scalar) element of the result array is what the formula would evaluate to using the corresponding (scalar) element(s) of the array argument(s).

You could think of this in functional progamming terms. Excel essentially turns a formula like:

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

into a function:

f(x) = IF([x], 42, 99)

which it then maps over a list of scalar x values:

map(f, {1,0,1})

to get {42,99,42}. Multiple array arguments just mean the conceptual function has more arguments:

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

and then:

map(f, {1,3}, {2,4})

evaluates to {2, 12}.

If the multiple array arguments are not the same length, Excel will try to expand them. Thus,

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

evalues to {1,2,3,#N/A} because {1,1,1} got expanded to {1,1,1,#N/A} and IF(#N/A, 4, 8) evaluates to #N/A.

This description covers most of the examples I can come up with. It also shows me that I don't need a function-by-function description of how array arguments are handled. Functions like SUM or AND don't work like IF or INDEX, because they don't necessarily expect scalar arguments. (There do remain loose ends, though: INDEX({2,3,5,7,11}, {2,4}) evaluates to {3,7} if called from the worksheet, but only to 3 if called through VBA using Application.Evaluate. And SUM(INDEX({2,3,5,7,11}, {2,4})) evaluates to 3 regardless of where and how it's called.)

I just can't believe Microsoft or one of the major Excel authors never published anything on this other than examples, helpful as they are. Hopefully the discussion of "How Microsoft Excel Expands Array Formulas" that Charles Williams mentioned in his answer is the "official" source I'm looking for.

Answer 1

Documentation usually covers what the function will do, not always it's use cases for every possible formula. MSFT has a number of pages on array formulas; these two are probably the best introductions:

• Putting basic array formulas to work
• Putting advanced array formulas to work

The later includes a bit on how to use array formulas with INDEX.

Using your example of INDEX, we see that a table was created that looks like this:

     Col 1  2  3  4
Row
   1     1  2  3  4
   2     5  6  7  8
   3     9 10 11 12
   4    13 14 15 16

Based on the Index query, you asked for Rows 1 and 3 and Columns 2 and 4 (the Excel formula is INDEX(array, row_num, [column_num])). So the result of the first query (Row 1, Column 2) is 2 and the second query (Row 3, Column 4) is 12.

If instead you had put {1,4} for rows and {4,1} for columns your result would be 4 and 13 because 4 is the value of (Row 1, Column 4) and 13 is the value of (Row 4, Column 1).

Answer 2

Even the most basic functions can be used with array arguments, eg. LOG()...

=INDEX(LOG(A1:C3),2,2)

but I've never actually seen this documented anywhere

Answer 3

I very much doubt that any such documentation about the behaviour of Excel functions with arrays exists.

When Excel used to come with manuals there was an interesting section called
"How Microsoft Excel Expands Array Formulas"
That explained the rules about handling combinations of missmatched array sizes and scalars etc, but I can't find that information in Excel help or on the web.
There was also a section entitles Special Functions Help You Work with arrays that included a list of functions that work with arrays (although I do not believe this gives you what you are looking for).
EDIT

I have added descriptions of Excel's array expansion rules to my array formulae page at http://www.decisionmodels.com/optspeedj.htm