Excel INDEX Function: Syntax, Examples, and Common Uses

Yes. In the English-language version of Microsoft Excel, the function is called INDEX. It returns the value—or, in some cases, the reference—at a specified position within a range, making it useful for retrieving data by row and column or combining with MATCH for flexible lookups.

What INDEX means in Excel

INDEX is a lookup and reference function, but it does not search a range for a word or number by itself. Instead, you give it a range and tell it which position to return. Think of a range as a small grid: INDEX counts rows and columns inside that grid, then returns the item at their intersection.

This distinction matters because the position is relative to the range you supply, not necessarily the worksheet’s row or column number. If the range starts at B2, the first item in that range is position 1, even though it is on worksheet row 2. INDEX does not automatically identify a matching name; MATCH or another lookup function can find the position first.

INDEX is available in current Excel for Microsoft 365 on Windows, Mac, and the web, as well as many earlier Excel releases. It is not a Microsoft 365-only function. Its basic behavior is consistent across platforms, although the function name and argument separator can depend on Excel’s language and regional settings.

INDEX syntax and its two forms

Excel supports an array form and a reference form. The array form is the one most people use for ordinary lookups. Its syntax is INDEX(array, row_num, [column_num]). The array is the range to retrieve from, row_num is the position down that range, and column_num is the position across it. The column argument is optional when the array has only one column.

The reference form is INDEX(reference, row_num, [column_num], [area_num]). It is designed for a reference that may contain more than one area, such as several separate ranges. The optional area_num selects which area to use. Most worksheets do not need this form; the array form is simpler for a single continuous table.

Square brackets in Excel’s displayed syntax mean an argument is optional. Do not type the brackets into a formula. In a typical English-language installation, arguments are separated by commas. Some regional settings use semicolons instead, so a formula copied from an English example may need its separators changed if Excel reports a formula error.

How row and column positions work

For a two-dimensional range, row_num counts from the top of the selected range and column_num counts from its left edge. Both positions start at 1. For example, in a range B2:D5, position 1,1 means B2; position 2,1 means B3; and position 1,2 means C2. These are positions within B2:D5, not absolute worksheet coordinates.

With a single-column range, INDEX needs only the row position. With a single-row range, it needs the column position; in that situation, supply the position as the third argument and use 1 for the row argument. A zero for row_num or column_num can request an entire row or column from the array. In modern Excel with dynamic arrays, the returned values can spill into neighboring cells; older versions may require legacy array-formula handling for some such uses.

Keep these points in mind when choosing the position:

Count from the first cell of the range you passed to INDEX, not from worksheet row 1 or column A.
Use 1 for the first row or column in the selected range.
For a two-dimensional range, specify both a row and a column to get one cell’s value.
Make sure the requested position exists inside the range; asking for a row or column beyond its size can return an error.

Practical INDEX examples

For a one-column example, assume B2:B5 contains 18, 22, 25, and 28, in that order. With those values in B2:B5, this formula requests the third item in the range:

=INDEX(B2:B5,3)

The result is 25, which is in B4. INDEX counts B2 as position 1, B3 as position 2, and B4 as position 3.

For a two-dimensional example, assume B2:D5 contains these rows: 18, 23, 31; 22, 27, 35; 25, 30, 39; and 28, 34, 42. The formula below asks for the third row and second column within B2:D5:

=INDEX(B2:D5,3,2)

The result is 30, the value in C4. Although C4 is on worksheet row 4 and column C, its position inside the specified range is row 3, column 2.

You can also retrieve a value from a row chosen by MATCH. Assume A2:A5 contains Pen, Notebook, Folder, and Ruler, while B2:B5 contains their prices: 1.25, 4.50, 2.00, and 0.75. If E2 contains Notebook, this formula finds its exact position in the product list and returns the corresponding price:

=INDEX(B2:B5,MATCH(E2,A2:A5,0))

The result is 4.50. MATCH returns 2 because Notebook is the second item in A2:A5; INDEX then returns the second item in B2:B5. The 0 argument tells MATCH to look for an exact match.

Using INDEX with MATCH for flexible lookups

INDEX and MATCH are often paired because they divide the work: MATCH locates a position, and INDEX retrieves the item at that position. The lookup range and return range can sit on different sides of each other, unlike the traditional VLOOKUP requirement that the lookup column be at the left edge of its table. This can make an existing workbook easier to maintain when columns are rearranged.

The combination can also find an intersection using two MATCH functions. Assume A2:A4 contains Lee, Mina, and Omar; B1:D1 contains Q1, Q2, and Q3; and B2:D4 contains the corresponding quarterly figures. If Mina’s row is 12, 15, 13, and F2 contains Mina while G2 contains Q2, use:

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

The result is 15: the first MATCH selects Mina’s second data row, and the second selects Q2’s second column. INDEX returns the cell where those positions meet. This pattern is useful for reports where someone chooses both a person or product and a period.

For a new formula in a Microsoft 365 or Excel 2021 workbook, XLOOKUP may be more straightforward for a simple one-direction lookup. INDEX with MATCH remains useful for compatibility with older Excel versions and for two-way lookups. Choose based on the workbook’s audience and the layout of its data, rather than assuming that one formula is always best.

Common INDEX errors and how to fix them

A common mistake is counting from the worksheet’s row number. If the range begins at A5, its first item is still row position 1. Another is leaving out the column number for a multi-column range when the goal is to identify a particular cell. A formula can be syntactically valid yet return a value from the wrong position if either argument is miscounted.

Check the range dimensions before troubleshooting the result. If an array has four rows, a row_num of 5 is outside it; if it has three columns, a column_num of 4 is outside it. An out-of-range position can produce #REF!. A row or column argument that is not a usable number can produce #VALUE!. If INDEX is paired with MATCH and the lookup item is absent, MATCH commonly returns #N/A, which then flows through the combined formula.

When a formula gives the wrong answer, check the following:

Confirm that the lookup and return ranges begin on corresponding rows.
Check that MATCH uses 0 when an exact match is intended.
Look for extra spaces or differing text in the lookup value and source list.
Verify that the INDEX row and column positions fit the selected array.
Check whether Excel expects commas or semicolons between arguments.

English function names and regional settings

If you are asking whether INDEX is the English name, yes: that is the function name used in English-language Excel. In installations using another display language, Excel may show localized function names. Regional settings can also affect whether formulas use commas or semicolons as argument separators. The workbook’s formula may therefore look different from an English example even when it performs the same calculation.

To enter the function without guessing its arguments, use Excel’s Insert Function feature and search for INDEX. Excel displays the available arguments and descriptions for the installed version and language. You can also begin typing the function name in a formula cell and use the autocomplete suggestions. If a formula copied from a website is rejected, first check the function name and separator style before changing the range references.

Frequently Asked Questions

What does INDEX do in Excel?
INDEX returns the value or reference at a specified row and, when needed, column position within a range. It does not search for a matching label unless paired with a function such as MATCH.

Is INDEX the same as MATCH?
No. MATCH returns the relative position of an item in a range. INDEX uses a position to return the corresponding value or reference. Together, they can perform a lookup.

Does INDEX work in older Excel versions?
Yes. INDEX is available in many older Excel releases, unlike newer functions such as XLOOKUP, which require a newer version. The exact interface and formula language can vary by release and locale.

Why does my INDEX formula return #REF!?
Usually, the requested row or column position is outside the dimensions of the selected range. Check that positions start at 1 within the range and do not exceed its number of rows or columns.

Can INDEX return a value to the left of the lookup column?
Yes, when combined with MATCH, INDEX can return a value from a separate range on either side of the lookup range. The return range must still line up with the matching positions.

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.