Excel INDEX Function Examples for Everyday Lookups

Excel’s INDEX function returns the value at a specified row and column within a range. For example, with sales data in B2:C5, =INDEX(B2:C5,3,2) returns the value in the third row and second column of that range; INDEX is especially useful for flexible lookups when combined with MATCH.

What the INDEX function does

INDEX answers a positional question: given a range, which value sits at the row and column you specify? Its basic array form is INDEX(array, row_num, [column_num]). The row and column numbers count from the top-left corner of the selected range, not from the worksheet’s row and column labels. Thus, row 1 means the first row of the range even if that range begins on worksheet row 20.

The array can be a single row, a single column, or a rectangular block of cells. For a one-column range, you can give a row number and leave out the column number. For a one-row range, you can specify a column number. In a two-dimensional range, provide both numbers to identify one cell. The result is the cell’s value, so text, numbers, and dates can all be returned.

INDEX has another, less commonly used reference form that can select from one or more reference areas. Most everyday formulas use the array form, particularly when INDEX is paired with MATCH. It is useful to recognize that INDEX can work with references as well as values, but you do not need the reference form for the examples below.

Example 1: Return a value by row and column

Suppose a small sales table has headers in A1:C1 and these records in A2:C5: North, 120, 135; South, 90, 110; East, 150, 145; and West, 80, 95. The columns are Region, January, and February. To retrieve East’s February sales, count within the sales range B2:C5: East is on its third data row, and February is its second column.

With January and February sales in B2:C5, enter =INDEX(B2:C5,3,2). The formula returns 145. The row number 3 refers to the third row inside B2:C5, which is worksheet row 4; it does not mean worksheet row 3. The column number 2 refers to column C within the selected range.

This positional approach is handy when a report already tells you a row and column number, or when those numbers are calculated elsewhere. If someone changes the range’s starting row, the formula still counts from the new range’s top-left cell. However, hard-coded positions can become outdated if rows are inserted, deleted, or reordered. For a lookup based on a name or label, MATCH is usually a better way to find the position.

Example 2: Return an item from a single column

INDEX can also return a value from a one-column list without a column number. With regions in A2:A5—North, South, East, and West—in that order, the second item is South.

With the region list in A2:A5, enter =INDEX(A2:A5,2). The result is South. This can be useful when a separate calculation supplies the position, such as a ranking or a row number produced by another formula.

A common variation is to use ROWS to calculate the size of a list and return its last position. With the same region list in A2:A5, enter =INDEX(A2:A5,ROWS(A2:A5)). ROWS counts four cells, so INDEX returns the fourth item, West. This returns the last position in the stated range, not necessarily the last nonblank cell if the range contains blanks or extends beyond the data.

Example 3: Combine INDEX and MATCH for a lookup

MATCH finds the position of a value in a range; INDEX uses that position to return a value from a corresponding range. This separates the task of finding a record from the task of choosing which result to return. It also means the lookup column does not have to be the leftmost column of a larger table, unlike with a traditional VLOOKUP setup.

Suppose region names are in A2:A5 and January sales are in B2:B5, matching the four-row sales example above. Put East in E2. With names in A2:A5, sales in B2:B5, and the lookup value in E2, enter =INDEX(B2:B5,MATCH(E2,A2:A5,0)). MATCH searches for East in the name range, and the final 0 requests an exact match. East is the third item, so INDEX returns 150.

The lookup and result ranges need to have the same number of rows and corresponding records in the same order. If a row is inserted into only one of those ranges, the formula may no longer pair the right name and sales amount. Selecting full matching columns or, better, using a consistently sized Excel Table can help keep the ranges aligned as records are added.

Example 4: Make a two-way lookup

A two-way lookup finds a record by row label and column header. For example, the sales table has regions in A2:A5, month headers in B1:C1, and the sales figures in B2:C5. Put East in E2 and February in F2. The formula can use one MATCH to locate East’s row and a second MATCH to locate February’s column.

With regions in A2:A5, month headers in B1:C1, values in B2:C5, East in E2, and February in F2, enter =INDEX(B2:C5,MATCH(E2,A2:A5,0),MATCH(F2,B1:C1,0)). The first MATCH returns 3 because East is the third region; the second returns 2 because February is the second month header. INDEX therefore returns 145.

This construction is useful for cross-tab reports where users choose both a category and a period. You can place the selection cells in a small control area, or use data-validation drop-down lists so users can select valid labels. The labels in the lookup cells must match the labels in the source ranges. Extra spaces or slightly different spelling can cause an exact-match formula to return an error.

Using INDEX with horizontal data and changing ranges

The same principles apply when the lookup labels run horizontally. MATCH can search a row of labels for a position, and INDEX can return a value from a corresponding row. For instance, a monthly report might put months across the top and total sales in a row beneath them. The position returned by MATCH identifies which column INDEX should use.

For data that changes size, Excel Tables can make formulas easier to maintain. Select the source data and choose Insert > Table, or press Ctrl+T on Windows. A table expands as records are added, and structured references such as a table’s named columns can make a formula easier to read than a series of cell addresses. On a Mac, Ctrl+T may differ depending on keyboard settings; use Insert > Table if the shortcut does not open the table dialog. The INDEX and MATCH logic itself is the same on Windows and Mac.

In Microsoft 365, formulas can also work with dynamic arrays, but INDEX is not itself a replacement for a clear source layout. Keep headers unique, avoid merging cells in the data area, and store one record per row when possible. Those habits make lookups easier to audit and reduce the chance that an inserted heading or blank row disrupts your ranges.

Common INDEX problems and how to fix them

#REF!: The requested row or column is outside the selected range, or the referenced cells were deleted. Check that the position is within the range’s dimensions. A three-row range cannot return row 4.
#N/A in an INDEX-and-MATCH formula: MATCH could not find the lookup value. Confirm spelling, spaces, number-versus-text type, and that the lookup value is actually present. For a straightforward exact lookup, use 0 as MATCH’s third argument.
A plausible but incorrect answer: The INDEX range and MATCH range may be offset or in different orders. Check that the first item in the lookup range corresponds to the first item in the return range, and that the ranges have matching dimensions.
Wrong row or column: INDEX counts positions relative to its selected range. Recount from its top-left cell rather than using worksheet row numbers. For a two-way lookup, check that the row MATCH searches the row labels and the column MATCH searches the headers.
Formula separators differ: The formulas here use commas, as requested for standard English-language Excel syntax. Some regional settings use semicolons as argument separators. If Excel rejects a formula with commas, check the list-separator setting for that installation.

When debugging, evaluate the MATCH pieces separately in unused cells. If a MATCH returns the expected position, then check the INDEX range and dimensions. This makes it easier to tell whether the issue is finding the label or returning the corresponding value.

INDEX and MATCH compared with other lookup options

INDEX with MATCH remains valuable when a workbook must work in older Excel versions, when the lookup and return columns are arranged flexibly, or when a formula needs independent row and column matches. Its main drawback for newer users is that the formula can look more complicated than a dedicated lookup function, especially when both dimensions are involved.

XLOOKUP can make a single-direction lookup more readable in Microsoft 365 and Excel 2021 or later. For example, it can search one range and return a value from another without requiring the return range to be to the right of the search range. XLOOKUP is not available in Excel 2019 and earlier, so INDEX with MATCH is a useful compatibility choice for files shared with those versions. VLOOKUP is another familiar option, but it searches the first column of its table array and returns a column to its right, which can be restrictive when a workbook’s layout changes.

There is no universal reason to replace a working lookup. Choose a formula that the workbook’s users can maintain and that is supported by their Excel versions. For a simple new workbook on Microsoft 365, XLOOKUP may be easier to read; for an older shared workbook or a two-way grid, INDEX and MATCH can be a practical fit. Regardless of the function, keep lookup values and source data consistent and test formulas against a few known records.

Frequently Asked Questions

What is INDEX used for in Excel?
INDEX returns a value from a range based on its relative row and, when needed, column position. It is often paired with MATCH to look up a value by its label.

Does INDEX count worksheet rows or range rows?
It counts from the first row and column of the range supplied to INDEX. In a range beginning at B2, row number 1 means worksheet row 2.

Why use INDEX and MATCH instead of VLOOKUP?
INDEX and MATCH can search a lookup range and return a value from a separate range without requiring the return column to be to the right. They can also be combined for a two-way lookup.

Can INDEX return the last value in a list?
Yes, if you give it the list’s final position. For a range with no gaps, ROWS(range) can supply that position. If the range may contain blanks or extra unused cells, first identify the last populated item rather than assuming the final cell is the last value.

Is INDEX available in older Excel versions?
INDEX and MATCH are longstanding Excel functions and are available in older releases as well as Microsoft 365. Newer alternatives such as XLOOKUP require Excel 2021 or Microsoft 365; confirm compatibility before sharing a workbook with users on older versions.

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.