INDEX returns the value at a specific row and column within a range or array. You tell it where to look, and it hands back the item at that position.
=INDEX(array, row_num, [column_num])What does INDEX do?
INDEX is a lookup and reference function that pulls a value out of a table by position. You give it a range, a row number, and an optional column number, and it returns whatever sits at that spot. Think of a seating chart: row 3, seat 5 gives you one specific person, and INDEX does the same for cells.
Most people meet INDEX when VLOOKUP feels limiting. VLOOKUP can only look to the right, breaks when columns are inserted, and needs the lookup column at the edge. INDEX works from any position and pairs naturally with MATCH to build flexible lookups that survive layout changes.
A common office situation: a sales manager has a grid of monthly figures by region and needs the number for the West region in March. Instead of scanning across and down, INDEX with the right row and column numbers returns it instantly, and the same formula can be copied to build a small dashboard.
When to use INDEX
- Pull a single value from a large table by row and column position.
- Build a two-way lookup with MATCH to find a value by row label and column label.
- Return the nth item in a list, such as the third product in a price table.
- Extract a value from a named range or dynamic array without sorting.
- Create flexible dashboards where users pick a row and column and INDEX fetches the result.
- Replace fragile VLOOKUP formulas that break when columns are inserted.
INDEX syntax
=INDEX(array, row_num, [column_num])| Argument | Required? | What to enter |
|---|---|---|
array | Required | The range or array of cells to look in. Example: B2:D10. |
row_num | Required | Which row of the array to return, counting from the top of the array. Example: 2 for the second row. |
column_num | Optional | Which column of the array to return, counting from the left. If omitted, INDEX returns the whole row. Example: 3 for the third column. |
What it returns: The value at the intersection of the specified row and column within the array. If column_num is omitted, it returns the entire row as an array.
INDEX examples
1 Example 1: Get a value by row and column
A small product table lists items in column A and prices in column B. The goal is to return the price of the third product. INDEX looks at the range B2:B6, counts down three rows to B4, and returns 25. Because there is only one column, the column argument is unnecessary.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Price | |||
| 2 | Pen | 2 | 25 | ||
| 3 | Notebook | 5 | |||
| 4 | Stapler | 25 | |||
| 5 | Folder | 8 | |||
| 6 | Tape | 3 | |||
| 7 |
2 Example 2: Two-way lookup with row and column numbers
A monthly sales grid has regions down column A and months across row 1. The goal is the value for the East region in March. INDEX uses the range B2:D5, row 2 (East is the second region row), and column 3 (March is the third month column). It returns 1800.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Jan | Feb | Mar | |||
| 2 | North | 1200 | 1350 | 1500 | 1800 | ||
| 3 | East | 1600 | 1700 | 1800 | |||
| 4 | South | 1100 | 1250 | 1400 | |||
| 5 | West | 1900 | 2000 | 2100 | |||
| 6 |
3 Example 3: INDEX with MATCH for a label-based lookup
Hard-coded row numbers break when data moves. Here MATCH finds the row for "West" in A2:A5 and the column for "Feb" in B1:D1, and INDEX uses those positions. MATCH returns 4 for West and 2 for Feb, so INDEX returns the value at row 4, column 2 of B2:D5, which is 2000.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Jan | Feb | Mar | |||
| 2 | North | 1200 | 1350 | 1500 | 2000 | ||
| 3 | East | 1600 | 1700 | 1800 | |||
| 4 | South | 1100 | 1250 | 1400 | |||
| 5 | West | 1900 | 2000 | 2100 | |||
| 6 |
4 Example 5: Look up a price by product name
A price list has product codes in column A and prices in column C. The goal is the price for product code "P-300". MATCH finds "P-300" in A2:A6 at position 3, and INDEX returns the third value from C2:C6, which is 12.5. This pattern is safer than VLOOKUP because the price column can sit anywhere.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Code | Product | Price | |||
| 2 | P-100 | Pen | 2 | 12.5 | ||
| 3 | P-200 | Notebook | 5 | |||
| 4 | P-300 | Stapler | 12.5 | |||
| 5 | P-400 | Folder | 8 | |||
| 6 | P-500 | Tape | 3 | |||
| 7 |
Common INDEX errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #REF! | The row_num or column_num is larger than the array, or the array reference is invalid. | Check that row_num is between 1 and the number of rows, and column_num is between 1 and the number of columns. |
| #VALUE! | row_num or column_num is not a number, often because a MATCH inside INDEX found no match and returned #N/A. | Wrap MATCH in IFERROR or verify the lookup value exists in the lookup range. |
| #N/A | A nested MATCH did not find the lookup value, so INDEX received an error as its position. | Confirm spelling, extra spaces, and that the lookup range contains the value; use TRIM or exact match. |
| Wrong value returned | Row or column numbers are counted from the start of the array, not from the worksheet row 1. | Count positions relative to the top-left cell of the array you passed to INDEX. |
Tips for using INDEX
- Pair INDEX with MATCH for lookups that work in any direction and survive inserted columns.
- Use a whole column range like
A:Aonly when necessary; smaller ranges are faster and less error-prone. - Remember that row_num and column_num are positions inside the array, not worksheet row numbers.
- Omit column_num to return an entire row, which is useful with dynamic arrays.
- Name your ranges (Formulas > Define Name) to make INDEX formulas easier to read.
- Test with a known value first, then replace hard-coded numbers with MATCH.
Frequently asked questions
What is the difference between INDEX and VLOOKUP?
VLOOKUP searches the leftmost column and returns a value to the right. INDEX returns a value by row and column position, and with MATCH it can look left, right, up, or down without depending on column order.
Can INDEX return multiple values?
Yes. If you omit column_num, INDEX returns the whole row. In modern Excel with dynamic arrays, you can also pass arrays of row or column numbers to return several values at once.
Why does INDEX return #REF!?
The row or column number is outside the array. For example, =INDEX(A1:B5,6,1) asks for row 6 in a five-row range, so Excel returns #REF!.
Does INDEX work with text, dates, and numbers?
Yes. INDEX returns whatever is in the cell: text, numbers, dates, or even errors. The data type is preserved.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. INDEX reference