Excel INDEX Function Examples: Useful Formulas and Uses

Excel’s INDEX function returns a value—or a reference to a value—from a range based on its row and column position. With item names in A3:A6 and region names in B3:B6, =INDEX(B3:B6,2) returns the second region, East; the examples below show how to use INDEX for tables, two-way lookups, and more.

What the INDEX function does

INDEX is useful when you know where a value sits in a range, or when another formula can find its position. Instead of searching for a label by itself, INDEX takes a row number and, when needed, a column number, then returns the value at that intersection. The position is counted relative to the range you give it, not relative to the worksheet.

For example, if the array is A3:D6, its first row is worksheet row 3 and its first column is worksheet column A. In that array, row 2 and column 3 identify cell C4. This relative numbering is an important detail: INDEX(A3:D6,2,3) points to the second row and third column of the selected array, not to worksheet cell C2.

INDEX is commonly paired with MATCH. MATCH finds the position of a label; INDEX uses that position to return the corresponding value. Together, they can make lookups that are more flexible than older VLOOKUP formulas, including lookups where the result is to the left of the search column. INDEX can also return a whole row or column in versions of Excel that support dynamic arrays.

INDEX syntax and its arguments

For the most common array form, the syntax is INDEX(array,row_num,[column_num]). The array is the cell range to read. Row_num specifies a position within that range, and column_num specifies a column position when the array has more than one column. The column argument is optional for a single-column range.

Assume the worksheet has this small data table: A2:D2 contains the headers Product, Region, Units, and Sales. Rows 3 through 6 contain Tea, West, 18, 90; Coffee, East, 25, 175; Tea, East, 31, 155; and Cocoa, West, 12, 84. This layout is used in several examples below. In a single-column range, a row number alone is enough:

With product names in A3:A6, =INDEX(A3:A6,2) returns Coffee. The value 2 means the second item inside A3:A6, which is the worksheet cell A4.

For a rectangular range, provide both positions. With the table in A3:D6, =INDEX(A3:D6,3,2) returns East. The third row of the selected range is worksheet row 5, and its second column is B.

Row_num or column_num can be zero to request an entire row or column from the array. In Microsoft 365, a formula that returns multiple values can spill into neighboring empty cells. For example, with the table in A3:D6, =INDEX(A3:D6,0,4) returns the fourth column of that array: 90, 175, 155, and 84. Enter it in a clear area with room for four results. Older Excel versions may require array-formula entry for results that return multiple cells.

Practical INDEX formula examples

These examples use the sample table described above. For each formula, the row and column numbers refer to positions inside the range named in that formula.

Return a value from one column: With product names in A3:A6, =INDEX(A3:A6,4) returns Cocoa, the fourth product.
Return a value from a table: With the table in A3:D6, =INDEX(A3:D6,2,4) returns 175, the second row and fourth column of the selected range.
Return the last item in a filled, continuous list: With every cell in A3:A6 populated and no blanks within the list, =INDEX(A3:A6,COUNTA(A3:A6)) returns Cocoa. COUNTA counts the four nonempty cells, and INDEX returns item four. This approach assumes there are no gaps; if blanks occur inside the range, COUNTA may not identify the position of the last nonblank cell.
Return a whole column: With the table in A3:D6, =INDEX(A3:D6,0,3) returns the values in the third column: 18, 25, 31, and 12. In Microsoft 365, the results spill when there is enough space.

These examples illustrate why the range matters as much as the position. If you change A3:D6 to A2:D6, the same row number now refers to a different worksheet row because the header is included in the array. Decide whether headers belong in the range before setting row or column numbers.

Use INDEX with MATCH for flexible lookups

MATCH returns the relative position of a lookup value. Its third argument, 0, requests an exact match. Nesting MATCH inside INDEX lets the formula locate a row by its label rather than relying on a fixed row number.

With product names in A3:A6 and sales figures in D3:D6, =INDEX(D3:D6,MATCH("Coffee",A3:A6,0)) returns 175. MATCH finds Coffee in the second position of A3:A6, and INDEX returns the second value in D3:D6.

You can find a value at the intersection of a matching row and a matching column. With product names in A3:A6, sales headers in C2:D2, and the Units and Sales data in C3:D6, =INDEX(C3:D6,MATCH("Tea",A3:A6,0),MATCH("Sales",C2:D2,0)) returns 90. The first MATCH finds the first Tea, which is the first row of the data range. The second finds Sales, the second column of C3:D6. Because Tea appears twice, this formula returns the first matching Tea row, not the second.

To make the lookup choices editable, place the product to find in F2 and the heading in F3. With product names in A3:A6, the Units and Sales headings in C2:D2, and values in C3:D6, =INDEX(C3:D6,MATCH(F2,A3:A6,0),MATCH(F3,C2:D2,0)) returns the value selected by those two cells. If F2 contains Tea and F3 contains Sales, it returns 90, following the first-match behavior. This arrangement is easier to reuse than changing text inside the formula each time.

INDEX reference form and multi-area ranges

INDEX has an array form and a reference form. The examples so far use the array form, which returns a value or an array of values. The reference form can select from multiple separate areas and has an additional area_num argument. It is less common in everyday lookups, but can help when a formula needs to choose among nonadjacent ranges.

With the first area A3:A6 and the second area C3:C6, =INDEX((A3:A6,C3:C6),2,1,2) returns 25. The final argument, 2, selects the second area; row 2 and column 1 of that area identify C4. Because this syntax is less familiar and can be harder to maintain, a clearly arranged helper range or separate INDEX formula may be simpler for a workbook used by others.

Common INDEX problems and how to fix them

When an INDEX formula returns an unexpected result or an error, check the range and the position arguments first. Common causes include:

Wrong relative position: INDEX counts from the top-left of the range. Check whether the range includes a header row or starts on the row you expect.
#REF!: A row or column number is outside the dimensions of the selected range, or a referenced cell range has been deleted. For A3:D6, valid row positions are 1 through 4 and valid column positions are 1 through 4.
#N/A from a combined formula: MATCH may not find the lookup value. Check for spelling differences, extra spaces, and whether the lookup range actually includes the value. With MATCH’s third argument set to 0, the match must be exact.
Wrong result when a label repeats: MATCH returns the first matching position in its lookup range. If duplicates are possible, decide which occurrence you need or use a method designed to return multiple matches.
#SPILL!: A formula returning an array cannot place its results because one or more destination cells are occupied. Clear the cells in the spill area, or use a single-cell formula if you only need one result.

Also check that the row lookup range and the INDEX result range cover the same records in the same order. For example, matching A3:A6 against INDEX range D2:D5 offsets the records by one row and can return a plausible but incorrect value. Keeping corresponding ranges aligned is essential in INDEX-MATCH formulas.

INDEX versus XLOOKUP and VLOOKUP

For a simple lookup in Microsoft 365, XLOOKUP is often more direct because one function can specify the lookup range and return range. XLOOKUP also supports returning values from columns to the left or right of the lookup range. It is available in Microsoft 365 and Excel 2021 or later; it is not available in some older Excel releases.

INDEX with MATCH remains useful when working in older versions that do not include XLOOKUP, when a workbook already uses that pattern, or when you need to build a flexible two-way lookup. VLOOKUP is familiar and can be convenient when the lookup column is the first column in the table, but it normally returns a value from a column to its right. Choose a formula based on the workbook’s Excel versions, layout, and clarity for the people who will maintain it.

Frequently Asked Questions

Does INDEX return a value or a cell reference?
In its array form, INDEX is generally used to return a value or an array of values. Its reference form can return a reference, which is useful in some advanced formulas. For typical lookups, think of INDEX as returning the value at a position in the supplied range.

Can INDEX look up a value by itself?
INDEX does not search for a label. It needs a position, such as a row number, or a position supplied by another function. Pair it with MATCH when you want Excel to find the position of a label automatically.

What does zero mean in an INDEX formula?
A zero for row_num or column_num requests the entire corresponding row or column of the array. In current Microsoft 365 Excel, multiple results can spill into adjacent cells. The other argument still determines which row or column is returned.

Why does INDEX-MATCH return the first duplicate?
MATCH with 0 returns the position of the first exact match it encounters. If a lookup label appears more than once, ordinary INDEX-MATCH returns the result associated with that first occurrence. A different formula approach is needed to return all matching rows.

Is INDEX-MATCH better than XLOOKUP?
Neither is best for every workbook. XLOOKUP is usually shorter for a straightforward lookup and is available in Microsoft 365 and Excel 2021 or later. INDEX-MATCH is valuable for compatibility with older Excel versions and for established workbooks that already use it.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.