Excel INDEX Array Formula: How It Works and When to Use It

Formulas & functions · Updated November 18, 2026

In Excel for Microsoft 365, INDEX can return a value from a range or return an array of values; when that array is meant to spill, enter the formula normally. Older Excel versions may require Ctrl+Shift+Enter for formulas that calculate with arrays, but a basic INDEX lookup does not automatically need an array formula.

What the INDEX function does

INDEX retrieves a value—or, in some situations, a reference or array—from a specified position in a range. You provide a range and a row number, and optionally a column number. The position is counted within the range, not by the worksheet’s row and column labels. For example, row number 1 means the first row of the selected range even if that range begins on worksheet row 20.

For a range with one or more rows and columns, the usual array form is INDEX(array, row_num, [column_num]). The square brackets in this description mean that column_num is optional; they are not typed into a formula. If the range has one column, you can give just the row number. If it has one row, you can give the column number. For a two-dimensional range, provide both to identify a cell.

INDEX is often combined with MATCH. MATCH locates the position of a search item, and INDEX returns the value at that position. This gives a flexible lookup pattern that can search in a column and return a result from another column, including when the return column is to the left of the search column.

What “array formula” means in Excel

An array is a collection of values that Excel processes together. An array formula is a formula that works with one or more such collections. The term can cause confusion because INDEX itself is not always being used as an array formula: a formula that asks INDEX for one cell normally returns one value. It is the calculation around INDEX, or a request for multiple values, that may make array behavior relevant.

Excel for Microsoft 365 supports dynamic arrays. When a formula returns several values and the destination cells are available, Excel can place the results in neighboring cells automatically. This is called spilling. In older Excel versions, some formulas that return or calculate arrays had to be confirmed as legacy array formulas by pressing Ctrl+Shift+Enter, often abbreviated CSE.

That difference matters when you follow an older tutorial. A CSE formula copied from a pre-dynamic-array version may work differently from a formula entered in Microsoft 365. Conversely, a formula that spills in Microsoft 365 may not be supported in an older Excel release. INDEX is available in many Excel versions, but dynamic spilling is a version capability, not a special feature exclusive to INDEX.

Basic INDEX lookup with MATCH

Suppose A2:A5 contains the names Mina, Omar, Lia, and Noah, while C2:C5 contains their sales figures: 120, 95, 140, and 110. To find Mina’s sales, MATCH finds Mina’s position in the names range, and INDEX uses that position in the sales range. With those values in the stated cells, enter this formula in any empty cell:

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

The result is 120. The final 0 in MATCH requests an exact match. The names and sales ranges are the same size and in the same order, so the position returned by MATCH points to the corresponding value in the sales range. This formula returns one value and does not need Ctrl+Shift+Enter in either modern Excel or typical legacy Excel.

For a lookup based on a name typed in another cell, replace the quoted name with that cell reference. For example, if the lookup name is in E2 and the same names and sales remain in A2:A5 and C2:C5, use:

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

This version updates when E2 changes. If the name is missing, MATCH returns #N/A, which INDEX passes through. You can handle that with IFNA if a friendlier message is useful; with the lookup value in E2 and the same ranges, enter:

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

Returning a value from a two-dimensional range

For a table, INDEX can use both a row position and a column position. Assume A2:C5 contains names in column A, departments in column B, and sales in column C. The row and column numbers refer to positions inside A2:C5: the first row is worksheet row 2, and the second column is worksheet column B. To return the value in the third row and second column of that range, use:

=INDEX(A2:C5,3,2)

The result is East if the third data row contains Lia in A4, East in B4, and 140 in C4. This example highlights an easy mistake: INDEX’s row_num is not necessarily the worksheet row number. Entering 4 as row_num would ask for the fourth row of A2:C5, which is worksheet row 5.

You can also let MATCH determine one or both positions. For instance, a MATCH against the name column can supply the row number, while a MATCH against the table’s headers can supply the column number. That approach is useful when a table’s columns may be rearranged, because the formula can locate a header rather than assuming a fixed column position.

INDEX with multiple conditions

An array formula can be useful when a lookup must meet more than one condition. Suppose A2:A6 contains regions, B2:B6 contains product names, and C2:C6 contains units. The rows are East/Pens/12, West/Pens/9, East/Paper/20, East/Pens/8, and West/Paper/15. To return the units for the first row where the region is East and the product is Pens, use:

=INDEX(C2:C6,MATCH(1,(A2:A6="East")*(B2:B6="Pens"),0))

The result is 12. Each comparison creates a set of TRUE or FALSE results. Multiplying the two sets turns rows meeting both conditions into 1 and other rows into 0; MATCH finds the first 1, and INDEX returns the corresponding units. In Microsoft 365, enter the formula normally. In versions without dynamic-array calculation, this kind of multiple-condition MATCH formula generally needs to be confirmed with Ctrl+Shift+Enter.

On Windows, Ctrl+Shift+Enter is the legacy array-entry shortcut. In Excel for Mac, the equivalent is also Control+Shift+Enter; depending on the keyboard, the final key may be labeled Return. Do not type braces around the formula: Excel displays braces around a legacy array formula after it has been entered correctly. If there are multiple matching rows, this formula returns the first one, not a total or every match.

Returning a whole row or column of values

INDEX can also be asked for an entire row or column by using zero for the relevant row or column number. For example, if C2:C5 contains 120, 95, 140, and 110, this formula requests the full column array:

=INDEX(C2:C5,0)

In Microsoft 365, entered in an empty cell with room below, the values can spill into four cells: 120, 95, 140, and 110. If cells in the spill area are occupied, Excel may show #SPILL!; clear the obstructing cells or move the formula. In older Excel versions, handling an array result may require selecting the intended output cells and using Ctrl+Shift+Enter, or using the returned reference in another formula. The exact result depends on how the formula is used and the version.

A zero row or column argument is not required for ordinary lookups, and it can make formulas less obvious to someone reading them. Use it when the goal really is to work with an entire row or column of values. For returning a filtered list based on conditions, Microsoft 365’s FILTER function may express the intent more clearly, but it requires Microsoft 365 or Excel 2021 or later.

INDEX array form and reference form

Excel has two forms of INDEX. The array form works with a single array or range and returns the value at the requested position. The reference form can work with a reference that contains multiple areas and includes an area_num argument to choose which area to use. Most everyday lookups use the array form, so the reference form is less likely to be needed.

This distinction explains why some descriptions say INDEX can return a reference rather than a value. Depending on the formula context, an INDEX result can be treated as a reference to a cell or range. For most users building a lookup, it is safest to think of the basic form as “select the item at this position” and use the reference form only when a formula specifically needs to choose among separate areas.

Common errors and how to fix them

  • #REF! usually means the requested row, column, or area position is outside the supplied reference. Check that the position is positive and within the range’s dimensions, unless you intentionally use zero to request an array.
  • #N/A commonly comes from MATCH when it cannot find the lookup value. Check spelling, extra spaces, data types, and whether the match mode is set to 0 for an exact match.
  • #SPILL! can appear when a dynamic array result has no clear space for its output. Remove blocking values, merged cells, or other obstructions from the spill area.
  • Wrong result from a valid formula often means the ranges are misaligned. The lookup and return ranges should cover corresponding records in the same order and normally have the same number of rows.
  • Unexpected row or column may mean the formula uses a worksheet row number where INDEX expects a position within the selected range. Count from the top-left corner of the supplied range.
  • Formula works in Microsoft 365 but not an older version may indicate that the formula relies on dynamic arrays or array evaluation that the older version handles differently. For a legacy multi-condition formula, try CSE entry if the Excel version supports that formula pattern.

INDEX and MATCH compared with other lookup choices

INDEX and MATCH remain useful when a workbook must work in older Excel versions, when the return column is to the left of the lookup column, or when the formula needs separate row and column matches. Their main drawback is that the combined formula can be less immediately readable than a dedicated lookup function.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later and can perform many common lookups with a single function, including looking left. For a simple exact lookup in a modern workbook, it may be easier to maintain. INDEX and MATCH can still be a sensible choice for compatibility or for two-dimensional lookups. Avoid choosing a formula only because it is familiar: consider the Excel versions used by the people who will open the workbook and whether the formula needs to return one result or an array.

Frequently Asked Questions

Does INDEX in Excel require Ctrl+Shift+Enter?
No. A normal INDEX formula that returns one value is entered with Enter. Ctrl+Shift+Enter is for certain legacy array formulas, such as older versions of a multiple-condition INDEX/MATCH formula. Microsoft 365 handles many array calculations without that shortcut.

How do I return an entire column with INDEX?
Use zero for the row number in the array form, such as =INDEX(C2:C5,0) when C2:C5 is the column of values. In Microsoft 365, the result can spill into nearby cells if there is space. Older versions may need array-formula handling or a formula context that accepts the array.

Why does INDEX return #REF!?
The requested position is usually beyond the size of the range. Check the row and column numbers against the range dimensions, and remember that INDEX counts positions from the start of the selected range rather than from worksheet row 1.

Can INDEX return a value to the left of the lookup column?
Yes. MATCH can find a position in one column, while INDEX returns the value in another range at that same position. The return range does not have to be to the right of the lookup range.

Is INDEX better than XLOOKUP?
Neither is always better. XLOOKUP is often more direct for a single modern lookup, while INDEX and MATCH can suit older-version compatibility, leftward lookups, and flexible two-way lookups. Choose based on the workbook’s Excel version and the job the formula must do.

Related

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.