INDEX

Return the value at a given row and column position in a range with Excel's INDEX function.

Lookup & Reference
|
Excel All versions
|
Google Sheets Supported

Spreadsheet editor

Syntax

=INDEX(array, row_num, [column_num]) Returns: Varies

Arguments

Argument Required Description
array Yes The range of cells or array from which you want to retrieve a value.
row_num Yes The row position within the array to return. Counts from the first row of the array. Set it to 0 to return the entire column.
column_num No The column position within the array to return. Needed when the array has more than one column. Set it to 0 to return the entire row.

About

INDEX returns the value sitting at a specific position in a range. You tell it the range, the row number, and (for a grid) the column number, and it hands back whatever is at that spot. The row and column numbers count from the first cell of the range, not from the top of the worksheet.

On its own INDEX is handy when you already know a position. Its real power shows up when you pair it with MATCH, which finds a position for you. Together, INDEX and MATCH can look up values in any direction, including to the left, which is something VLOOKUP cannot do.

Use INDEX when you need flexible lookups across rows and columns, two-way lookups in a grid, or when you want to pull a value by position from a list. In newer versions, XLOOKUP covers many of the same jobs, but INDEX with MATCH still works everywhere, even in older Excel versions.

Examples

Look up a value to the left with INDEX and MATCH

See how INDEX and MATCH pull a product code that sits to the left of the name you search for. Change the product in B7 and watch the code update, something VLOOKUP can't do.

Spreadsheet editor

Two-way lookup in a sales grid

Try changing the region and quarter to find the sales figure where they meet. One MATCH picks the row, the other picks the column, and INDEX returns the value at that spot.

Spreadsheet editor

Watch out for

⚠

Counting from the worksheet instead of the array

People expect row_num 5 to mean worksheet row 5, then get the wrong value.

→ Row and column numbers count from the first cell of the array. In =INDEX(B10:B20, 1), the 1 points to B10, not row 1 of the sheet.

⚠

Position outside the range returns #REF!

Asking for row 12 in a 10-row range, or a column that doesn't exist, gives a #REF! error.

→ Keep row_num and column_num within the size of the array. When pairing with MATCH, this usually means the lookup value wasn't found, so check your data.

⚠

Missing the column number in a grid

Using only row_num on a range with several columns returns the whole row as an array, not a single cell.

→ Add column_num when the array spans more than one column, for example =INDEX(B2:E13, 5, 3) to land on one cell.

⚠

Swapping the row and column arguments

In INDEX and MATCH setups it's easy to put the column MATCH where the row MATCH belongs, returning a value from the wrong spot.

→ The order is row first, then column. The MATCH that searches down a column feeds row_num; the MATCH that searches across a row feeds column_num.

Tips & notes

INDEX actually comes in two forms. The common one, shown above, is the 'array form': =INDEX(array, row_num, [column_num]), where the first argument is a single range. The second is the 'reference form': =INDEX(reference, row_num, [column_num], [area_num]), where the first argument can be several non-adjacent ranges grouped in parentheses, like (A1:A10, C1:C10). The names array and reference refer to the same first slot, just in these two different forms.

The extra area_num argument only applies to the reference form. It picks which of the grouped ranges to look in (1 for the first, 2 for the second, and so on), so =INDEX((A1:A10, C1:C10), 3, 1, 2) returns the third cell of the second range, C3. Leave area_num out and INDEX defaults to the first range.

One more trick: INDEX returns an actual reference, not just a value, so you can drop it into the middle of a range. =SUM(B2:INDEX(B2:B100, 10)) sums B2 down to whatever cell INDEX points at, which is handy for building ranges that grow or shrink based on a formula.

Common questions

Should I use INDEX/MATCH or VLOOKUP?

INDEX with MATCH can look up values in any direction, including to the left, and stays fast on large sheets. VLOOKUP is simpler to write but only searches left to right from the first column.

Can INDEX return more than one cell?

Yes. Set row_num to 0 to return an entire column, or column_num to 0 to return an entire row. The result is an array you can feed into functions like SUM or AVERAGE.

Why does INDEX give a #REF! error?

The row or column number is outside the range you gave it. Double-check the position, and if you're using MATCH, confirm the lookup value actually exists in the data.

Does INDEX work in Google Sheets?

Yes. Google Sheets supports INDEX with the same syntax, so formulas carry over without changes.