INDEX - Return the value at a position
Read a row-and-column intersection, then combine INDEX with an exact MATCH to retrieve a department budget without moving columns.
By the end, you can
- Count rows and columns within an array.
- Combine a key search with a value retrieval.
- Recognize the difference between INDEX and a matching function.
Before you start: Know cell ranges and 1-based positions. MATCH finds a position from a key.
In this lesson
Position is the input, value is the result
INDEX returns an item from a range at a supplied position. On a single budget column, INDEX with position 3 reads the third budget. It does not search for Operations itself. MATCH or XMATCH can find that department's relative position, which INDEX then uses to read the aligned budget.
Array form and arguments
=INDEX(array, row_num, [column_num])This lesson starts with the array form. For a two-dimensional range, specify both coordinates. With a single-column range, row_num alone identifies the item.
| Argument | Purpose | Example |
|---|---|---|
arrayRequired | The range or array from which to retrieve. | B2:C5 |
row_numRequired | 1-based row position, required for the vertical examples here. Excel allows omission when column_num supplies the coordinate, such as a single-row array. | 2 |
column_numOptional | 1-based column position, needed when selecting a column in a matrix. | 2 |
Find a department budget
₹1,50,000| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Department | Monthly | Quarterly | Requested | Monthly result | |
| 2 | Sales | ₹1,20,000 | ₹3,60,000 | Operations | ₹1,50,000 | |
| 3 | Finance | ₹85,000 | ₹2,55,000 | Finance | ₹85,000 | |
| 4 | Operations | ₹1,50,000 | ₹4,50,000 | HR | ₹60,000 | |
| 5 | HR | ₹60,000 |
Direct coordinates and a left return
=INDEX(B2:B5,3)Position 3 is worksheet B4, not worksheet row 3.
=INDEX(B2:C5,2,2)Second row and second column within B2:C5 selects Finance's quarterly budget in C3.
=INDEX(A2:A5,MATCH(150000,B2:B5,0))Find the ₹1,50,000 monthly amount and return Operations from the aligned name vector. Equal budgets would need a tie rule.
Coordinates, missing matches, and reference form
- Row and column positions are relative to array, starting at 1. Out-of-range coordinates produce #REF! rather than searching for a nearby item.
- A missing exact MATCH contributes #N/A to INDEX. Investigate mismatched labels or handle a genuinely absent department deliberately.
- In Excel's array form, a zero coordinate can return an entire row or column; dynamic-array Excel can spill that result. Zero does not mean the first item.
- Excel also has INDEX(reference,row_num,[column_num],[area_num]) for selecting a reference area. This is separate from the single-area array form shown here.
INDEX and MATCH are available in older and current Excel. Neither the retrieval vector's side nor column order dictates lookup direction, provided the searched and retrieved positions align.
Common mistakes and Practice limitations
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.