ARRAYFORMULA({1,2,3})
Generates three cells with the values 1, 2, 3 in them.
A bit more complex:
ARRAYFORMULA(VLOOKUP(A2,Sheet1!A:F,{1,2,3,4},false))
Uses columns 1..4 from the vlookup and fills columns with data from those cells.
What I really want is:
ARRAYFORMULA(VLOOKUP(A2,Sheet1!A:F,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21},false))
I'm wondering if there is an easy way to generate the parameters that are entered as arrays of numbers, in this case the {1,2,3,…21}. So, I want some way to generate {1,2,3} or {1,2,3,4,5} or {1,2,3,…,125} without having to type all those numbers since they are in a sequence.
Is there a formula something like:
SEQUENCE(1,21) that generates {1,2,3,4,5, ... , 19,20,21} or
SEQUENCE(1,1000) that generates {1,2,3,4,5, ... ,998,999,1000}
So that ultimately I can enter:
ARRAYFORMULA(VLOOKUP(A2,Sheet1!A:F,SEQUENCE(1,21),false)) or
ARRAYFORMULA(VLOOKUP(A2,Sheet1!A:F,SEQUENCE(1,1000),false))
To clarify: I am not looking for a script solution. I am trying to find something that works in-cell.
Best Answer
Short answer
Use something like
ROW(A1:A21)Explantation
ROW(cell_reference) returns the row number of the specified reference. When the reference is a range and the function is inside of a ARRAYFORMULA it will return a an array of a sequence of numbers starting on the row number corresponding to the start cell reference and ending on the row number corresponding to the end cell reference.
There are several Q&A on this site that use this.