How does ArrayFormula work?

Question

I'm trying to understand when does ARRAYFORMULA decide to expand a range, and when does it decide to repeat it.

According to this StackOverflow question:

The rules for ARRAYFORMULA are:

• If a function parameter inside ARRAYFORMULA is expected to be a scalar (criterion in the examle) and a range is given it will expand in the cells below iterating this range.
• If a function parameter inside ARRAYFORMULA is expected to be a range (range and sumrange) and a range is given it will pass on the range to the formula.

I wonder if it really is the case.

As an example, this is our table:

Name	Count	Lookup	Result
Alice	3	John	5
Bob	2	Marta	1
Frank	4
John	5
Marta	1

The Result column contains the following formula:

=ArrayFormula(VLOOKUP(FILTER(C2:C, LEN(C2:C)), A2:B, 2, FALSE))

For every name in column C, it searches the name in column A and returns the count.

Somehow, ArrayFormula knows that the vlookup should happen for every cell in C2:C, but that the argument A2:B should stay intact (to search in the whole table).

Why doesn't ArrayFormula expand the lookup range too, and searches C2 in A2:B2, C3 in A3:B3 etc (which means it wouldn't find anything)?

Answer 1

I'm going to attempt to answer your question by modifying/clarifying the explanation given in the previous answer to more closely match your example. The 'rules' of ARRAYFORMULA are:

• If a function parameter inside ARRAYFORMULA is expected to be a scalar (VLOOKUP's search_key or index arguments in the example) and an array or range is supplied, an array output will be given whose dimensions match those of the input.
• Any function parameters inside ARRAYFORMULA which require an array or range by default (VLOOKUP's range in the example) are not changed.

So what you state at the end of your question ('Somehow, ArrayFormula knows that the vlookup should happen for every cell in C2:C, but that the argument A2:B should stay intact') is entirely correct given the above.

In the specific case of ARRAYFORMULA I find Google's documentation to be very poor, and a lot is left to the end-user to work out for themself.