INDEX Excel Arguments Explained: Syntax and Examples

In Excel, INDEX returns a value or reference at the intersection of a specified row and column in a range. Its arguments tell Excel which array or reference to search and which position to return; the reference form also lets you choose between multiple ranges.

What INDEX does and why its arguments matter

INDEX is a position-based function. Instead of searching for a text or number and deciding where it appears, you give INDEX a range and position numbers. Excel counts rows and columns within that range, starting at 1, and returns the item at the requested position. That makes INDEX useful for direct retrieval and as part of a lookup formula.

The function has two forms, called the array form and the reference form. Their first arguments look similar, but the forms differ in how they interpret the input and in whether you can specify an area number. The formal signatures are INDEX(array, row_num, [column_num]) and INDEX(reference, row_num, [column_num], [area_num]). These are syntax templates, not formulas to enter; the names in them stand for ranges or position numbers. Square brackets mark optional arguments.

For example, the second row and third column of a three-column range means its item at relative position row 2, column 3. These are positions inside the range, not necessarily worksheet row 2 and column 3. If the range starts at B5, its first cell is still position 1,1.

Array form: array, row_num, and column_num

In the array form, array is the range or array of values from which INDEX returns an item. row_num gives the item’s relative row position, and the optional column_num gives its relative column position. For a two-dimensional range, provide both positions to identify one cell. Excel returns an error if a specified position falls outside the array’s dimensions.

Assume A2:C4 contains a 3-by-3 block of values, with 10 in A2, 20 in B2, 30 in C2, 40 in A3, 50 in B3, 60 in C3, 70 in A4, 80 in B4, and 90 in C4. The formula is:

=INDEX(A2:C4,2,3)

This returns 60: row 2 within A2:C4 is worksheet row 3, and column 3 is column C. The example illustrates the key distinction between relative positions and sheet coordinates.

If the array has only one row or one column, the position for its other dimension can be omitted. With numbers in B2:B6, for instance, a row position alone selects an item from that vertical range. When working with a two-dimensional array, omitting a position can be less clear, so supplying both row and column numbers is usually easier to read.

A zero can be used for a row or column position to request an entire column or row from the array, rather than a single cell. With Microsoft 365 or a version of Excel that supports dynamic arrays, the returned values can spill into neighboring cells when they are unobstructed. Older Excel versions may require entering some array-returning uses as legacy array formulas. For ordinary single-cell lookups, use nonzero row and column positions.

Reference form and the area_num argument

The reference form uses reference for one or more ranges, followed by relative row and optional column positions. Its extra argument, area_num, identifies which range, or area, to use when the reference contains multiple ranges. The first area is numbered 1, the second 2, and so on. If there is only one area, area_num is optional and defaults to 1.

This form is useful in less common setups where a formula must choose from several separate blocks. It is not the same as selecting a worksheet tab: an area is one of the ranges supplied to INDEX. For example, if the reference contains two blocks, an area number of 2 tells INDEX to use the second block before applying the row and column positions. Make sure the requested positions exist in the selected area; otherwise Excel returns an error.

Most everyday formulas use the array form because it is straightforward and works naturally with a single rectangular range. If you are unsure which form a formula needs, start with a single range and the array form. Reach for the reference form only when choosing among multiple ranges is part of the requirement.

Using INDEX to retrieve a value by position

INDEX can retrieve a value without a separate lookup function when you already know the position. Suppose A2:C4 contains the values 10, 20, 30 in its first row, 40, 50, 60 in its second, and 70, 80, 90 in its third. To get the first item in the third row of that range, use:

=INDEX(A2:C4,3,1)

The result is 70, because row 3 and column 1 within A2:C4 is cell A4. This kind of direct position lookup is handy when row and column numbers are themselves calculated elsewhere. The returned result is only as current as the range and position arguments supplied; INDEX does not infer which item you intended.

It can also be used to select a position from a list. With product names in A2:A6 and the desired list position 4 in D1, this formula returns the fourth product name:

=INDEX(A2:A6,D1)

If D1 contains 4, Excel returns the value in A5. If D1 is zero, negative, or greater than the number of items in the range, the position is invalid. Validating a user-entered position can prevent errors in a worksheet designed for other people to use.

Combining INDEX with MATCH for lookups

INDEX is frequently paired with MATCH. MATCH finds the relative position of a value in a lookup range; INDEX uses that position to retrieve the corresponding item from another range. For an exact match, set MATCH’s third argument to 0. This pairing can look up values without requiring the lookup column to be the leftmost column of a larger table.

Suppose employee names are in A2:A6 and their sales totals are in B2:B6: A2 is Mira, A3 is Leon, A4 is Ava, A5 is Noah, and A6 is Inez; the corresponding totals are 640, 520, 710, 480, and 590. To return the sales total for Mira, use:

=INDEX(B2:B6,MATCH("Mira",A2:A6,0))

MATCH finds Mira in the first position of A2:A6, and INDEX returns the first value of B2:B6, which is 640. If a name is not present, MATCH returns an error and that error passes through the formula. If a name appears more than once, exact-match MATCH returns the first matching position, so the formula returns the first corresponding total.

The lookup and return ranges should have matching dimensions for a straightforward one-to-one lookup. If the lookup range covers five rows but the return range starts at a different row or has a different number of rows, the position may point to the wrong record. Check that the first item in each range belongs to the same record.

Two-way lookups with row and column matches

INDEX can also return a value by finding both a row position and a column position. Suppose A2:A4 contains the regions West, East, and North; B1:C1 contains the headers Q1 and Q2; and B2:C4 contains the values 120, 150 for West, 200, 180 for East, and 90, 110 for North. To find East’s Q2 value, use:

=INDEX(B2:C4,MATCH("East",A2:A4,0),MATCH("Q2",B1:C1,0))

The first MATCH returns 2 because East is the second region. The second MATCH returns 2 because Q2 is the second header. INDEX therefore returns row 2, column 2 within B2:C4, which is 180. This structure is valuable when a table’s row or column can move: the MATCH functions locate labels rather than relying on hard-coded positions.

Keep the lookup ranges aligned with the data block. The region labels must correspond, in order, to the rows in B2:C4, and the quarter headers must correspond to its columns. If someone inserts a new quarter or region, the label searches can still find the right position as long as the ranges include the added data.

Choosing INDEX or another lookup function

INDEX is a good choice when you need a position-based return, a two-way lookup, or a flexible formula that combines with MATCH. It also separates locating a position from retrieving a value, which can make the logic easier to adapt. On its own, however, INDEX does not search for a label; it needs a position number or another function that supplies one.

For users with Microsoft 365 or Excel 2021 and later, XLOOKUP is often a simpler option for a basic one-direction lookup. It searches a lookup range and returns a corresponding value, so a separate INDEX and MATCH pair may not be necessary. VLOOKUP can also handle common lookups, but its table layout and column-index argument can be restrictive when the return column is to the left of the lookup column. INDEX with MATCH is still useful in older Excel installations and in models where positional flexibility matters.

Choose based on the task and the Excel versions used by the people who will open the workbook. XLOOKUP is not available in Excel 2019 or Excel 2016. INDEX and MATCH are supported in older versions, making them a practical compatibility option. A newer function is not automatically clearer if the people maintaining a workbook are more familiar with INDEX and MATCH.

Common INDEX errors and how to fix them

#REF! usually means a requested row, column, or area is outside the supplied range, or a referenced cell or range was deleted. Compare the position numbers with the range’s dimensions.
#VALUE! can appear when a row, column, or area argument is not a valid numeric position. Check that an argument does not contain unexpected text.
The wrong value appears: remember that positions are counted from the top-left of the supplied range, not from worksheet row 1 or column A. Confirm the range begins where you think it does.
A lookup returns an error: if INDEX is combined with MATCH, check the lookup spelling, hidden spaces, and whether MATCH is set to 0 for exact matching. Also verify the lookup and return ranges are aligned.
A whole row or column does not appear: zero requests an entire row or column, but Excel’s display depends on how the formula is entered and the version in use. In dynamic-array Excel, check that the spill area is empty; in older versions, array-entry behavior differs.

When auditing a complicated formula, test the MATCH part by itself in an empty cell first. If it returns the expected position, check the INDEX range and its dimensions. Breaking the formula into these parts often reveals whether the issue is the search, the range alignment, or the requested position.

Frequently Asked Questions

What does row_num mean in INDEX?

It is the relative row position within the range supplied to INDEX, counted from the range’s first row as 1. It is not necessarily the worksheet’s row number.

Can INDEX return a value from a column?

Yes. Give INDEX a vertical range and the relative row position. For a two-dimensional range, include the column position as well when you need to identify a particular cell.

What is the difference between INDEX array and reference forms?

The array form takes an array and returns an item from it. The reference form takes one or more references and adds area_num so you can specify which range to use. For a single rectangular range, the array form is typically sufficient.

Why does my INDEX formula return #REF!?

Check whether its row or column number exceeds the supplied range, whether an area number refers to an area that was not supplied, or whether a referenced range was deleted. All position numbers are relative to the selected range.

Is INDEX MATCH better than XLOOKUP?

Neither is universally better. XLOOKUP is often simpler for a basic lookup in Microsoft 365 or Excel 2021 and later, while INDEX with MATCH works in older Excel versions and handles flexible row-and-column lookup patterns.

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.