Spreadsheet Practice

How to use INDEX to return a value by row and column number

Formula (Excel and Google Sheets)

=INDEX(B2:F6,I1,K1)

Give INDEX the range, then the row and column positions inside it: =INDEX(B2:F6,I1,K1).

Result on the example
Eli in I2
Works in
Excel 2024 and Microsoft 365
Google Sheets
Same formula

See it work

The formula is already in I2. Change any input and the result updates, or edit the formula to experiment.

How it works

INDEX counts inside the range you give it: row 3 of B2:F6 is Week 3, and column 4 is Thursday. The cell where they meet holds Eli. The positions don't depend on where the table sits on the sheet.

=INDEX(B2:F6,I1,K1)

Result"Eli"

  1. B2:F65 rows × 5 cols: "Ana", "Ben", "Cara", "Dev", …

    A range: 5 rows × 5 columns.

  2. I13

    The value in I1.

  3. K14

    The value in K1.

  4. INDEX(B2:F6,I1,K1)"Eli"

    INDEX: Value at a given row and column of a range.

    • arrayB2:F65 rows × 5 cols: "Ana", "Ben", "Cara", "Dev", …
    • row_numI13
    • column_numK14

In Google Sheets

The same formula works in Google Sheets: =INDEX(B2:F6,I1,K1). We ran it in Google Sheets mode on the same data and it returns Eli.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=CHOOSEROWS(CHOOSECOLS(B2:F6,K1),I1)EliPicks the column, then the row. Excel 2024, Microsoft 365 and Google Sheets.

Common mistakes

  • Row and column numbers outside the range return #REF!.
  • The numbers count from the range's top-left cell, not from A1. Row 3 of B2:F6 is sheet row 4.
  • A row or column of 0 returns the whole column or row, which spills.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Use INDEX to return a value by row and column number

In I2, return who is on duty in the week number in I1 on the day number in K1 (1 is Monday).

Questions

How do I get the numbers from labels?

Use MATCH for each: =INDEX(B2:F6,MATCH("Week 3",A2:A6,0),MATCH("Thu",B1:F1,0)).

How do I return a whole row?

Use 0 for the column: =INDEX(B2:F6,I1,0).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.