# How can I create a formula to generate a range in Google Sheets?

``````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

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 '19 at 21:51
• @Ninjakannon Or to specify the the row: `ROW(A:A)` :) – Rubén Apr 6 '19 at 23:08

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