Excel INDEX Formula Examples for Rows, Columns, and Matches

Excel’s INDEX function returns the value at a chosen position in a range. With values in A2:C5, =INDEX(A2:C5,3,2) returns the entry in the third row and second column of that range—cell B4. INDEX is especially useful for retrieving data by position or pairing with MATCH to build flexible lookups.

What the INDEX function does

INDEX looks inside a range and returns the value at the intersection of a specified row and column. Its row and column numbers are positions within the range, not worksheet row and column numbers. For example, row 1 of A2:C5 means worksheet row 2, while column 2 means worksheet column B.

Excel has two forms of INDEX. The array form is the one most people use: it takes an array or range, a row number, and optionally a column number. The reference form works with one or more separate ranges and can also take an area number to choose which range to use. Both forms return a value or reference; they do not alter the source data.

INDEX is a lookup function, but it does not search for a label by itself. You give it a position directly, or use a function such as MATCH to find the position first. That separation is useful: MATCH answers “where is this item?” and INDEX answers “what value is at that position?”

Basic INDEX formula examples

Suppose A2:C5 contains this small inventory table: column A has Pen, Notebook, Marker, and Folder; column B has Stationery, Paper, Stationery, and Filing; and column C has prices 1.50, 4.00, 2.25, and 3.50. With that layout, this formula selects the third row and second column within A2:C5:

=INDEX(A2:C5,3,2)

The result is Stationery, which is in B4. The row number 3 counts from the top of the selected range, and the column number 2 counts from its left edge. If you instead wanted the price in the same relative row, the third column would be selected.

INDEX can also work with a single-column range, where only the row position is needed. With prices in C2:C5 in the order listed above, this returns the second price:

=INDEX(C2:C5,2)

The result is 4.00, from C3. For a one-row range, give the column position instead. With month names in B1:D1 as January, February, and March, =INDEX(B1:D1,1,2) returns February. Specifying the row as 1 makes the formula’s intent clear even when the range has only one row.

Use INDEX with MATCH for a lookup

To look up a price by product name, MATCH can find the product’s row and INDEX can return the price from that row. In the inventory layout above, enter Marker in F2. With product names in A2:A5, prices in C2:C5, and the lookup value in F2, use:

=INDEX(C2:C5,MATCH(F2,A2:A5,0))

The result is 2.25. MATCH finds Marker in the third position of A2:A5; INDEX then returns the third value in C2:C5. The final MATCH argument, 0, requests an exact match. For text or IDs, exact matching is usually what a lookup needs.

The separate ranges must line up: the first item in the return range must correspond to the first item in the lookup range, the second to the second, and so on. If one range starts on row 2 and the other on row 3, the formula may return a plausible but incorrect result. Keeping both ranges the same height helps avoid that mistake.

Build a two-way row-and-column lookup

INDEX can select a value based on both a row label and a column heading. Imagine a report with regions in A2:A4 (North, South, West), months in B1:D1 (Jan, Feb, Mar), and sales values in B2:D4. The rows contain North: 120, 135, 128; South: 98, 110, 117; and West: 140, 132, 150. Put South in F2 and Feb in G2. With the row labels, headings, and values in those ranges, use:

=INDEX(B2:D4,MATCH(F2,A2:A4,0),MATCH(G2,B1:D1,0))

The result is 110. The first MATCH returns the row position for South; the second returns the column position for Feb. INDEX uses both positions to select the intersection. This structure is useful for schedules, price grids, budgets, and any table where the relevant row and column may change.

When writing a two-way formula, check that the data block begins immediately below the column headings and beside the row labels. In this example, B2:D4 is the data block, A2:A4 contains its row labels, and B1:D1 contains its column labels. Matching ranges that are offset from the data block can silently point to the wrong intersection.

Return a value from a column to the left

Unlike VLOOKUP, an INDEX-and-MATCH lookup can return a result from a column to the left of the column being searched. Suppose employee names are in A2:A5, other details are in B2:B5, and employee IDs are in C2:C5. If E2 contains an ID such as E104, then, with the names and IDs in those ranges and the lookup ID in E2, use:

=INDEX(A2:A5,MATCH(E2,C2:C5,0))

The result is the name on the same row as E104. The return range A2:A5 is to the left of the lookup range C2:C5, which is permitted because MATCH identifies a position independently of the direction of the return range. This is a common reason to choose INDEX with MATCH instead of VLOOKUP.

Return a whole row or use multiple areas

In Microsoft 365 for Windows, INDEX can return an entire row from a selected range when its column number is 0. With the monthly values in B2:D4 described above, this formula selects the second row of that block:

=INDEX(B2:D4,2,0)

The result is the row of values 98, 110, 117. In a dynamic-array version of Excel, the values spill across three cells, provided those cells are empty. In older Excel versions without dynamic arrays, returning an array may require selecting the output cells and confirming the formula as an array formula with Ctrl+Shift+Enter. Excel for the web and current Microsoft 365 builds support dynamic arrays.

The reference form of INDEX can choose among separate ranges. For example, with one set of values in A2:A5 and a second set in C2:C5, this formula asks for the second cell in the second area:

=INDEX((A2:A5,C2:C5),2,1,2)

The result is the value in C3. In this form, the final argument, 2, selects the second area; the row and column arguments then specify a position within it. This form is less common than the array form, but it can be useful when a formula must select between separate ranges without first combining them.

Handle missing matches and common errors

If MATCH cannot find a lookup value, it returns #N/A, and the INDEX-and-MATCH formula also displays #N/A. You can replace that result with a clear message. Suppose names are in A2:A5, prices are in C2:C5, and the requested product is in F2. With those ranges and the lookup value in F2, use:

=IFNA(INDEX(C2:C5,MATCH(F2,A2:A5,0)),"Not found")

If F2 contains a product that is not listed, the result is Not found. IFNA handles the missing-match error specifically, rather than concealing every possible formula error. If the formula instead contains an invalid row or column position, INDEX can return #REF!. If a lookup unexpectedly returns the wrong value, first verify that the lookup and return ranges begin on corresponding rows and have the same number of entries.

#N/A: Check spelling, extra spaces, data types, and whether the lookup value exists. Exact MATCH uses 0 as its third argument.
#REF!: Check that the requested row or column position falls within the selected range and that referenced cells have not been deleted.
Unexpected zero: INDEX may be pointing to a blank cell. Confirm the position and whether the source cell is actually empty.
Wrong lookup result: Check for shifted ranges or duplicate lookup values. MATCH returns the first matching position it encounters.

INDEX with MATCH compared with other lookup functions

INDEX with MATCH remains a flexible pattern, particularly in workbooks that need to run in older Excel versions. It can search in one range and return a corresponding value from another range, including a range to the left. It also makes the lookup and return ranges explicit, which can be helpful when maintaining a complex worksheet.

XLOOKUP can make many single-direction lookups simpler because it combines the search and return ranges in one function and can specify a value to return when no match is found. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. INDEX and MATCH are a useful alternative when compatibility with older Excel releases matters, or when a formula already depends on separate row and column positions. For a two-way lookup, XLOOKUP can be nested, but INDEX with two MATCH functions expresses the row-and-column intersection directly.

VLOOKUP is straightforward for tables where the lookup column is at the left of the return column, but it cannot return a value to the left of its lookup column. INDEX with MATCH can. The best choice depends on the workbook’s Excel versions, the table’s structure, and which formula will be easiest for another person to understand and maintain.

Practical tips for reliable INDEX formulas

Use exact MATCH mode, 0, for ordinary text, names, product codes, and IDs. Approximate MATCH modes have sorting requirements and should be chosen deliberately.
Keep lookup and return ranges aligned and of equal length. For two-way lookups, ensure the data block and both label ranges describe the same rows and columns.
Use absolute references such as $A$2:$A$100 when copying a formula and you need the lookup range to stay fixed. Use relative references when you want the range to shift as the formula is copied.
Use Excel Tables for growing datasets when appropriate. Structured references can make formulas easier to read and can expand as rows are added, though the INDEX/MATCH logic still depends on matching corresponding records.
Test formulas with a value near the top, middle, and bottom of the source range, as well as a value that is absent. This helps expose shifted ranges and missing-match behavior.

Frequently Asked Questions

What does the row number in INDEX mean?
The row number is the item’s position inside the range passed to INDEX, not its worksheet row number. In A2:A10, a row number of 1 refers to A2, and a row number of 2 refers to A3.

Does INDEX search for a name or value?
No. INDEX retrieves the item at a position you specify. Pair it with MATCH when you want Excel to find the position of a lookup value first.

Why does INDEX with MATCH return #N/A?
Usually MATCH cannot find an exact match. Check the lookup value and source data for spelling differences, extra spaces, or numbers stored as text, and confirm that the formula uses 0 for exact matching.

Can INDEX return a value to the left of the lookup column?
Yes. Set INDEX’s return range to the column you want to retrieve, then use MATCH on the separate column containing the lookup values. The return range can be on either side.

Is INDEX better than XLOOKUP?
Not in every situation. XLOOKUP is often simpler for a one-direction lookup in Microsoft 365 or Excel 2021 and later. INDEX with MATCH is flexible, supports leftward returns, and works in older Excel 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.