Generates three cells with the values 1, 2, 3 in them.

A bit more complex:


Uses columns 1..4 from the vlookup and fills columns with data from those cells.

What I really want is:


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:


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
    Commented Jun 30, 2017 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
    Commented Jun 30, 2017 at 20:17

3 Answers 3


Short answer

Use something like ROW(A1:A21)


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.

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


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.


=ARRAYFORMULA( VLOOKUP(A2:A, Sheet2!A:Z, SEQUENCE(1,25,2,1), false))

SEQUENCE(1, [length], [start], [step]) returns a list (in this case a row) of numbers of length [length] starting with [start] incremented by [step].

Generally, if your lookup column is in the first position of your lookup table, [start] will be 2. Make sure the length you specify is the same as the number of columns in the lookup table, else you'll get an out of range error.

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.