# Excel INDEX Function: Syntax, Examples and How to Use It

The INDEX function in Excel returns the value at a position you specify inside a range or array. You give it the range, a row number and a column number, and it hands back whatever sits at that intersection. It is one of the most useful lookup tools in the program because it works by position, so it does not care whether your data is sorted.

## Quick Answer

- INDEX returns a value from a range by row and column position, not by matching a label.
- The basic form is `=INDEX(array, row_num, [column_num])`.
- `row_num` and `column_num` are counted from the top left corner of the range you pass in, starting at 1.
- If the range is a single row or a single column, you can leave out the other argument.
- INDEX pairs naturally with MATCH, which finds the position that INDEX then uses.

## Syntax

The full syntax is:

$$=INDEX(array, row\_num, [column\_num])$$

| Argument | Required? | Meaning |
|---|---|---|
| array | Required | The range or array you want to pull a value from. |
| row_num | Required in most cases | Which row of the array to look in, counted from the top of the array. |
| column_num | Optional | Which column of the array to look in, counted from the left of the array. |

There is a second form, the reference form, written as `=INDEX(reference, row_num, [column_num], [area_num])`. It behaves the same way but accepts multiple ranges and an area number to pick between them. Most everyday work uses the array form above.

## How It Works

INDEX does not search. It counts. When you write `=INDEX(B2:D10, 3, 2)`, Excel looks at the range B2:D10, moves down 3 rows from the top of that range and across 2 columns from the left, then returns the single cell at that spot.

Two details trip people up.

First, the counting is relative to the range, not to the worksheet. Row 1 of the range is the first row of the range, even if that row is worksheet row 2. Column 1 of the range is the first column of the range, even if that column is worksheet column B.

Second, when the range is one-dimensional, you can drop an argument. For a vertical list in A2:A10, `=INDEX(A2:A10, 4)` returns the fourth item. For a horizontal list in A2:J2, `=INDEX(A2:J2, 4)` returns the fourth item. In the horizontal case, Excel treats the single number as the column position.

If you set `row_num` to 0 and provide a `column_num`, INDEX returns the whole column of the array. If you set `column_num` to 0 and provide a `row_num`, it returns the whole row. This behavior is what makes INDEX useful inside other formulas, because it can hand a whole slice of a range to a function that expects an array.

INDEX also works with the results of other functions. You can nest it inside SUM to add a slice of values, or inside COUNT to count entries in a row or column you select by position.

## Worked Example

Suppose you have a small sales table. Column A holds product names, column B holds units sold and column C holds unit price. The table occupies A2:C5, with headers in row 1.

| | A | B | C |
|---|---|---|---|
| 1 | Product | Units | Price |
| 2 | Widget | 10 | 2 |
| 3 | Gadget | 20 | 3 |
| 4 | Sprocket | 30 | 4 |
| 5 | Bolt | 40 | 5 |

You want the price of the third product in the list. The range is A2:C5. The third product sits in the third row of that range. Price sits in the third column of that range.

$$=INDEX(A2:C5, 3, 3)$$

Excel counts 3 rows down from A2, landing on row 4 of the worksheet, then 3 columns across from A, landing on column C. The result is 4, the price of the Sprocket.

Now suppose you only want the units for the second product. Units are in column B, which is the second column of the range, and the second product is in the second row.

$$=INDEX(A2:C5, 2, 2)$$

The result is 20.

If you pass a single column instead, the formula gets shorter. For the units column alone:

$$=INDEX(B2:B5, 4)$$

The result is 40, the units for the fourth product.

## More Examples

**Return a value by position from a single column.** With a list of monthly totals in D2:D13, `=INDEX(D2:D13, 6)` returns the sixth month's total. This is the simplest use of the index Excel formula and the one to learn first.

**Return a value by position from a single row.** With quarterly figures in B1:E1, `=INDEX(B1:E1, 3)` returns the third quarter's figure.

**Combine INDEX with MATCH for a label lookup.** MATCH finds the position of a label, and INDEX uses that position. If product names are in A2:A5 and you want the price for "Gadget", you can write `=INDEX(C2:C5, MATCH("Gadget", A2:A5, 0))`. MATCH returns 2 because Gadget is the second item, and INDEX returns the second item of C2:C5, which is 3. This pattern is the standard alternative to VLOOKUP and it does not require the lookup column to sit to the left of the result column.

**Return a whole row or column.** `=INDEX(A2:C5, 0, 2)` returns the entire second column of the range, which is B2:B5. Wrapped in SUM, `=SUM(INDEX(A2:C5, 0, 2))` adds the units column. This is handy when the column you want changes based on another cell.

**Use a cell reference for the position.** If cell F1 holds the number 3, then `=INDEX(A2:C5, F1, 3)` returns the price in the third row. Changing F1 changes the result without editing the formula.

**Point at a different sheet.** INDEX accepts a range on another sheet, so `=INDEX(Sheet2!A2:C5, 2, 2)` returns the value at that position on Sheet2. If you need to build the range address from text, INDIRECT can supply it, though it is volatile and recalculates often.

## Errors and How to Fix Them

**#REF!** appears when `row_num` or `column_num` points outside the array. If your range has 4 rows and you ask for row 5, INDEX has nothing to return. Check that your position numbers are within the size of the range.

**#VALUE!** appears when `row_num` or `column_num` is not a number. A text value in the position argument causes this. Make sure the argument resolves to a number, or wrap it in a function that converts text to a number.

**#N/A** usually comes from a nested MATCH that found nothing, not from INDEX itself. If the lookup value is missing from the lookup range, MATCH returns #N/A and INDEX passes it through. Check the lookup value for extra spaces or a type mismatch between text and numbers.

**Wrong value, no error.** This is the most common problem. INDEX returns a value, but it is not the one you expected. The cause is almost always a range that starts in the wrong place, so the relative counting is off by one or more rows. Confirm the top left cell of the range you passed in.

## Common Mistakes

- **Counting rows from the worksheet instead of the range.** If your range is A2:C5 and you want the first data row, use row 1, not row 2. The fix is to count from the first cell of the range you actually passed.
- **Swapping row and column.** `=INDEX(A2:C5, 3, 2)` and `=INDEX(A2:C5, 2, 3)` return different cells. The fix is to remember the order: row first, then column.
- **Forgetting the column argument on a two-dimensional range.** If you pass a rectangular range and only one position number, Excel treats it as the row and returns the whole row as an array. The fix is to supply both numbers, or to pass a single-column range.
- **Using a hard-coded position that breaks when rows are inserted.** Inserting a row inside the range shifts the data but not your number. The fix is to combine INDEX with MATCH so the position is found dynamically.
- **Assuming INDEX needs sorted data.** It does not, because it works by position. The fix is to stop sorting your data just to make INDEX work, and instead check that your position numbers are correct.
- **Confusing INDEX with the INDEX and MATCH pair.** INDEX alone cannot find a value by label. The fix is to add MATCH when you are looking up by name or ID.

## Limitations

INDEX returns a value or a reference, but it does not search, filter or sort. If you do not know the position of the value you want, INDEX alone cannot help you. You need MATCH, or a function like OFFSET that can move relative to a starting point, or a lookup function that matches on a key.

INDEX also does not tell you when your position is wrong in a way that still lands inside the range. It will happily return a neighboring cell's value with no warning. That makes careful range definition more important than with functions that match on a key, because a matching function fails loudly when the key is absent while INDEX fails silently. When you build a lookup, test it against values you already know so you can confirm the positions line up.

## Frequently Asked Questions

### What is the difference between INDEX and MATCH?

INDEX returns a value at a position you give it. MATCH returns the position of a value you give it. Used together, MATCH finds the row or column number and INDEX uses it to return the value. This combination is more flexible than VLOOKUP because the return column can sit to the left of the lookup column.

### Can INDEX return more than one value?

Yes. If you set `row_num` to 0, INDEX returns the whole column of the array. If you set `column_num` to 0, it returns the whole row. In modern Excel these spill into neighboring cells. In older versions you need to enter the formula as an array formula or wrap it in a function that accepts an array.

### Does INDEX work with unsorted data?

Yes. INDEX works by position, so the order of the data does not matter. This is one reason it pairs well with MATCH using an exact match, which also does not require sorted data.

### Why does my INDEX formula return the wrong value?

The most likely cause is that your range starts in a different cell than you assumed, so your row or column count is off. Check the first cell of the range and count from there. A second common cause is swapping the row and column arguments.

### Can I use INDEX across multiple sheets?

Yes. You can pass a range on another sheet, such as `=INDEX(Sheet2!A2:C5, 2, 2)`. The reference form of INDEX also accepts multiple areas and an area number, which lets you choose between several ranges in one formula. For a broader tour of how functions fit together, see the Excel functions overview.

## References

This article draws on the standard references listed under Further Reading.

## Further Reading

- [Broman KW, Woo KH (2018). Data Organization in Spreadsheets. The American Statistician](https://doi.org/10.1080/00031305.2017.1375989)
- [Ziemann M, Eren Y, El-Osta A (2016). Gene name errors are widespread in the scientific literature. Genome Biology](https://doi.org/10.1186/s13059-016-1044-7)
- [Microsoft Support: Excel help and learning](https://support.microsoft.com/en-us/excel)
- [McCullough B, Heiser DA (2008). On the accuracy of statistical procedures in Microsoft Excel 2007. Computational Statistics & Data Analysis](https://doi.org/10.1016/j.csda.2008.03.004)
- [Abeysooriya M, Soria M, Kasu MS et al. (2021). Gene name errors: Lessons not learned. PLOS Computational Biology](https://doi.org/10.1371/journal.pcbi.1008984)
- [Wilson G, Bryan J, Cranston K et al. (2017). Good enough practices in scientific computing. PLOS Computational Biology](https://doi.org/10.1371/journal.pcbi.1005510)

## Related Articles

- [Excel TEXT Function: Syntax, Format Codes and Examples](/blog/data-analysis/excel-text-function)
- [Excel Functions: What They Are and How to Use Them](/blog/data-analysis/excel-functions-overview)
- [Excel FIND Function: Syntax, Examples and SEARCH Comparison](/blog/data-analysis/excel-find-function-syntax-examples)
- [Excel SUM Function: Syntax, Examples and Tips](/blog/data-analysis/excel-sum-function-examples)
- [COUNT Function in Excel: Syntax, Examples and Tips](/blog/data-analysis/count-function-in-excel)