-1

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)?

11
  • 1
    Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer.
    – Community Bot
    Apr 30, 2023 at 0:53
  • 1
    Welcome to Web Applications. When asking questions about Google Sheets formulas, please describe the spreadsheet structure and relevant settings i.e. the country as it affects the function parameter separator among other stuff, the timezone as it might affect import/export, and interactions with other apps like Google Apps Script. Include sample input data and the corresponding expected output, directly in the question body as text, include links to questions that you have found on this site, and a brief explanation about why they don't meet your needs. Apr 30, 2023 at 3:58
  • 1
    @Rubén Thanks! I have actually read it and upvoted long before I've asked the question :-) I'm trying to explain a bit better, but my question is very generic regarding ARRAYFORMULA expansion rules. Working on editing it. Might have figured it out, but the question is still worth asking I believe.
    – Bharel
    Apr 30, 2023 at 4:14
  • 1
    No, as it will require a lengthy answer. The best is to ask one question and wait to get at least one good answer, then "rinse and repeat" (ask a new question, and wait at least to get one good answer). On your 2nd and following questions mention the previous questions if they are directly related, if the new question is very similar to any previous question, yours or from others, clearly explain why it's being asked the second question (why it's "not a duplicate"). A common reason is to avoid having a question requiring a lengthy answer, another reason is to make follow-up questions. Apr 30, 2023 at 5:16
  • 1
    Detailed answers are good while they aren't lenthy. Apr 30, 2023 at 5:18

1 Answer 1

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.

2
  • So basically the function will be repeated for every scalar value combination (in case of multiple scalars) until the dimensions match the matrix of the scalar values?
    – Bharel
    Apr 30, 2023 at 19:09
  • 1
    I don't have a CompSci background so some of the terminology is right on the edge of my understanding, but I think this is correct. To go back to your example, if your VLOOKUP range was three columns rather than two, you could return the values in both the second and third columns corresponding to search_key C2:C by passing {2,3} to index (as C2:C is a column vector and {2,3} is a row vector so their dimensions are compatible) May 1, 2023 at 10:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.