Google Sheets: how to get the first (Nth) row / column from a range? (built-in functions)

Question

Say I have this named range, not A1.

idz01 idz04 ida02 foo a 1 b bar c 3 8 baz 8 2 g 

Now, how can I get the first row? And the Nth row? And how is the Nth column? Like a range using built-in functions.

Edit: Nth line: =OFFSET(ObjednavkyData, N,0, 1) Last line:

 =OFFSET(ObjednavkyData, MAX(ARRAYFORMULA(ROW(ObjednavkyData)))-ROW(ObjednavkyData), 0, 1 ) 

Answer 1

Just use the INDEX function:

 =INDEX(NamedRange1,NRow,NColumn) 

If you want to use the last row and column, you can use:

 =INDEX(NamedRange1,ROWS(NamedRange1),COLUMNS(NamedRange1)) 

• INDEX more effective than the alternative OFFSET and INDIRECT , which are unstable.

Examples:

 =INDEX(ObjednavkyData,3,2) //This will return "c". =INDEX(ObjednavkyData,ROWS(ObjednavkyData),COLUMNS(ObjednavkyData2)) //This will return "g". 

Addition:

If you want to get the whole row, you can omit the [column] part of the INDEX function. And if you need the whole column, omit the [row] part.

 =INDEX(ObjednavkyData,3) //This will return row 3: "bar c 3 8". 

Answer 2

Partial answer: (still open to the best)

1st row

 OFFSET(Data, 0 , 0, 1) 

First column:

You can just call some functions on a range, and they take the 1st column.
Or, if necessary:

 OFFSET(Data, 0, 0, MAX(ARRAYFORMULA(ROW(Data))), 1) 

Nth line:
The key point here is that OFFSET() filled only to the right and to the right. Therefore, you only need to crop these directions by these parameters.

 OFFSET(Data, N, 0, 1); 

It can also be achieved by feeding the first row to ARRAYFORMULA() and for each column (cell) to get the cell of the last row using INDEX(COLUMN(),ROW() + N) .

Nth column:

Similarly above, only you need to get the number of rows.

 OFFSET(Data, 0, N, ROWS(Data), 1); 

I played with TRANSPOSE() , but it seems that OFFSET() does not digest it well.

Answer 3

First line:

 =INDIRECT(COLUMN(data)&":"&COLUMN(data)) 

First column:

 =INDIRECT(CHAR(64+COLUMN(data))&":"&CHAR(64+COLUMN(data))) 

Nth line:

 =INDIRECT(COLUMN(data)+N&":"&COLUMN(data)+N) 

N'th Column:

 =INDIRECT(CHAR(64+COLUMN(data)+N)&":"&CHAR(64+COLUMN(data)+N)) 

Replace the data with your data range and N with the row / column that you need in the data. Row / Column counts starting at 0. You can change this purchase using N-1 .

If you want to get the actual range (A: A, 1: 1, A3: A3) instead of values, just get rid of INDIRECT in these formulas

Hope this helps

Answer 4

I would like to suggest an alternative method that also works with mutable unnamed ranges (for example, output from other functions, not just ranges existing in the spreadsheet). The general idea is to use FILTER () as an extraction mechanism, and build a vector of true / false values ​​as an indicator of which rows to retrieve. So, for example, if the original range is A1: D13, and I want to extract line 7, I would write

 =filter( A1:D13, {transpose(split(rept("0 ", 6), " ")); 1; transpose(split(rept("0 ", rows(A1:D13)-7), " ")) } ) 

As you can see, it also easily generalizes to extracting any number of rows from anywhere. In the general case, we construct the following formula:

 =filter( <data>, {transpose(split(rept("0 ", <number rows to skip from the start>), " ")); transpose(split(rept("1 ", <number of rows to extract>), " ")); transpose(split(rept("0 ", <number of rows to skip until the end), " ")) } )