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

  • It may not seem intutive at first, but most people use the =ROW() or =COLUMN() function to accomplish this sort of thing. If you leave =ROW() with no parameters it will assume it is being evaluated in each cell it is expressed within ( same with =COLUMN() ). If you explicitly list a range as that parameter the way that Ruben did it within an =ARRAYFORMULA it will return an array type result with the row#'s of the range you explicitly used as a parameter. SO: `=ARRAYFORMULA(ROW(1:21)) will return a 1x21 array of numbers counting from 1 to 21. – Xzila Jun 30 '17 at 20:16
  • The drawback is that if you need a very large list of numbers you need that many rows for the formula to work. BUT if you need a small list that happens to just be high numbers you can accompish this with addition for example =ArrayFormula(ROW(1:6)+1000) – Xzila Jun 30 '17 at 20:17
6

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.

  • You don't even need to specify the column: ROW(1:21) is equivalent. – Ninjakannon Apr 6 at 21:51
  • @Ninjakannon Or to specify the the row: ROW(A:A) :) – Rubén Apr 6 at 23:08
2

TRY THE FILTER FORMULA INSTEAD

I think you are doing this a harder way than it needs to be. The =Filter() formula here is an easier solution.

An example you listed is =ARRAYFORMULA(VLOOKUP(A2,Sheet1!A:F,{1,2,3,4},false)) This same thing can be acomplished: =FILTER(Sheet1!A:F,Sheet1!A:A=A2)

Let me know if this works, or if you have any questions and I will clarify my answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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