FormuladrillINDEX
INDEX(array, row_num, [column_num]) returns the value at a given row and column position inside a range. On its own it needs the position as a number; paired with MATCH, which finds that number, it becomes a lookup that works in any direction.
Syntax
INDEX(array, row_num, [column_num])| Part | What it is |
|---|---|
array | The range to pick from. A single column, a single row or a grid. |
row_num | Which row of array to return, counted from 1. Use 0 to return the whole column. |
column_num | Which column of array. Needed when array has more than one column. Use 0 for the whole row. |
Examples
=INDEX(A2:A50, 7)Result: The value in A8, the 7th cell of A2:A50
=INDEX(B2:F10, 3, 2)Result: The value in C4, row 3 and column 2 of the grid B2:F10
=INDEX(B2:F10, 0, 2)Result: The whole second column, C2:C10, which spills or can be wrapped in SUM
A 0 row number returns the entire column; 0 column number returns the entire row.
=INDEX(C2:C50, MATCH(E2, A2:A50, 0))Result: The column C value for the row where A equals E2
The usual lookup pattern.
Common mistakes
- #REF!: row_num or column_num is larger than the range, for example INDEX(A2:A10, 12).
- #VALUE! on a grid: you gave only a row number for a range with several columns. Add column_num.
- Returning the wrong row: array starts at row 2 but you passed a worksheet row number. INDEX counts from the first row of the range, not the sheet.
Practice INDEX
These drills check your answer on live data, free, no account needed.
- Price gridReturn the price for the size in G1 and the plan in G2 from the grid, in G3. Use INDEX with two MATCH functions.
- Look left with INDEX and MATCHThe employee name is in the last column, so VLOOKUP cannot reach the salary to its left. Use INDEX and MATCH to return the salary of the employee named in F1. Answer in F2.
Questions
What is INDEX used for?
Returning a cell from a range by position. Its main use is as the return half of an INDEX/MATCH lookup, and for picking the nth item from a list.
How do I get a value by row and column in Excel?
=INDEX(range, row, column), for example =INDEX(B2:F10, 3, 2). To find the row and column from labels, replace the numbers with MATCH.
Related
- MATCHHow the Excel MATCH function returns the position of a value in a row or column, with exact and approximate match types, examples and the causes of #N/A.
- INDEX MATCHHow to combine INDEX and MATCH in Excel to look up a value from any column, left or right, with worked examples, a two-way lookup and the usual errors.
- CHOOSEHow the Excel CHOOSE function returns one of several values based on an index number, with examples for weekday names, quarters and scenario switches.
- XLOOKUPHow XLOOKUP works in Excel: find a value in one column and return the match from another, in any direction, with a not found value and next-smaller match.