Google Spreadsheets: How to get a first (Nth) row/column from a range? (built-in functions)

Question

Say I have this named range, not at 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 Nth row? And how the Nth column? As a range, using the built-in functions.

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

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

Answer 1

Just use the INDEX function:

=INDEX(NamedRange1,NRow,NColumn) 

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

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

• INDEX is more efficient than the alternative OFFSET and INDIRECT that are volatile.

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 (by putting 0 in the row field).

=INDEX(ObjednavkyData,3)    //This will return row 3:    "bar       c      3      8". =INDEX(ObjednavkyData,0, 2) //This will return column 2: "idz01     a      c      8".