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

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

**Excel:** `=INDEX(B2:F6,I1,K1)` (Excel 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G | H | I | J | K |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Week | Mon | Tue | Wed | Thu | Fri |  | Week no. | 3 | Day no. | 4 |
| Week 1 | Ana | Ben | Cara | Dev | Eli |  | On duty |  |  |  |
| Week 2 | Fay | Gus | Hana | Ivo | Jade |  |  |  |  |  |
| Week 3 | Ben | Cara | Ana | Eli | Dev |  |  |  |  |  |
| Week 4 | Gus | Jade | Ivo | Fay | Hana |  |  |  |  |  |
| Week 5 | Cara | Ana | Dev | Ben | Eli |  |  |  |  |  |

Formula in I2: `=INDEX(B2:F6,I1,K1)`

Result: Eli

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

## Other ways

- `=CHOOSEROWS(CHOOSECOLS(B2:F6,K1),I1)`: Picks 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.

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

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/index-by-row-and-column-number