INDEX function

INDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.

=INDEX(array, row_num, [column_num])

Generate a INDEX formula

Describe what you need. The generator will reach for INDEX where INDEX is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How INDEX reads its arguments
arrayrequiredrow_numrequiredcolumn_numoptionalINDEX →
ArgumentRequiredDescription
arrayRequiredThe range (or array constant) from which to pull data; it can be any rectangular block of cells.
row_numRequiredA positive integer indicating which row of the array to return; if omitted the entire column is returned, and out-of-range numbers trigger #N/A.
column_numOptionalAn optional positive integer for the column within the array; if omitted the whole row is returned, and invalid numbers cause #REF!.

Returns

It returns a single value or a spilled array matching the shape of the requested row or column.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Find the budgeted amount for Groceries

BudgetedActualVariance
120012000
300350-50
15013020
100120-20
=INDEX(C3:E6, MATCH("Groceries", A3:A6, 0), 1)

Result: 300

MATCH locates "Groceries" in column A (row 2 of the array) and returns 2. INDEX then looks in the second row of the C3:E6 block and the first column of that block, which is the Budgeted column, yielding 300.

2. Retrieve the variance for Utilities

CategoryMonthBudgetedActualVariance
RentJan120012000
GroceriesJan300350-50
UtilitiesJan15013020
EntertainmentJan100120-20
=INDEX(A3:E6, 3, 5)

Result: 20

The array A3:E6 contains five rows; row number 3 within that array corresponds to the third data row, "Utilities". Column 5 is the Variance column, so INDEX returns the value 20.

3. Spill the entire row for Entertainment

CategoryMonthBudgetedActualVariance
RentJan120012000
GroceriesJan300350-50
UtilitiesJan15013020
EntertainmentJan100120-20
=INDEX(A3:E6, 4, )

Result: EntertainmentJan100120-20

By omitting the column_num argument, INDEX returns the whole fourth row of the supplied array. In Excel/Sheets this spills horizontally, producing the five-cell row that describes the Entertainment category.

Common errors

Which INDEX error are you seeing?
INDEX returned an error#N/A
Adjust row_num to a value between 1 and the array's row count, or use MATCH to generate a valid row number.
#REF!
Supply a column number that falls within the array's columns, or omit column_num if you want the whole row.
#VALUE!
Wrap the argument in VALUE() or ensure it comes from a numeric source such as a cell containing a number.
ErrorWhy it happensHow to fix it
#N/AThe row_num argument is larger than the number of rows in the array (or zero/negative).Adjust row_num to a value between 1 and the array's row count, or use MATCH to generate a valid row number.
#REF!The column_num argument exceeds the array's column width or is zero.Supply a column number that falls within the array's columns, or omit column_num if you want the whole row.
#VALUE!row_num or column_num is non-numeric (e.g., a text string that cannot be coerced to a number).Wrap the argument in VALUE() or ensure it comes from a numeric source such as a cell containing a number.

Tips and when to use something else

  • Combine MATCH with INDEX for a two-dimensional lookup when you need both row and column criteria.
  • Omit column_num to return an entire row; the result will spill horizontally in modern Excel and Google Sheets.
  • If you need a dynamic column lookup as well, consider XLOOKUP, which can replace both MATCH and INDEX in a single formula.
  • When you need a reference that moves relative to a cell, OFFSET can be more expressive than INDEX for positional offsets.

Related functions

VLOOKUPVLOOKUP searches the first column of a table and returns a value from a specified column in the same row, optionally using exact or approximate matching.XLOOKUPXLOOKUP returns the value from a specified return array that corresponds to a given lookup value, with optional handling for missing matches and search direction.MATCHMATCH returns the relative position of a lookup value within a one-dimensional range, optionally using exact, less-than, or greater-than matching.OFFSETOFFSET returns a reference to a range that is a specified number of rows and columns from a starting cell, optionally sized by height and width.CHOOSECOLSCHOOSECOLS returns the selected columns from a given array, preserving order and data types, and spills the result into adjacent cells.

Frequently asked questions

How do I use INDEX to return a whole column instead of a single cell?
Set the column_num to the desired column and omit row_num (or use 0) – e.g., =INDEX(A3:E6, , 3) returns the third column of the array as a vertical spill.
Can INDEX retrieve values from non-contiguous ranges?
No. INDEX requires a single rectangular array. To work with non-contiguous data you must first combine the ranges with CHOOSE or CHOOSECOLS, then apply INDEX.
Why does INDEX sometimes return a #SPILL! error?
When the formula is set to spill an entire row or column but adjacent cells are already occupied, the engine cannot place the results and shows #SPILL!. Clear the blocking cells or move the formula.
Is there a difference between INDEX and the older INDIRECT function for dynamic references?
Yes. INDEX works with actual cell references and is volatile-free, making it faster and less error-prone than INDIRECT, which evaluates a text string and recalculates on every change.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator