INDEX

Returns the content of a cell, specified by row and column offset.

Examples

Make a copy

Guest Name Dietary Restriction Sent Invitation Table Number
David Vegetarian No 3
Bob None No 5
david None Yes 1
Nancy None No 4
Mary Vegetarian Yes 2

 

Formula Formula Output
=INDEX(A2:D6, 2, 1) Bob
=INDEX(A2:D6, 4, 4) 4
=INDEX(A2:D6,1,1) David
=INDEX(A1:D6, 6, 2) Vegetarian

 

Formula Formula Output
=INDEX(A2:D6, 2, 0) Bob None No 5

 

Formula Formula Output
=INDEX(A2:D6, 0, 4) 3
5
1
4
2

Returns the cell(s) found by index value into the referenced range.

Sample Usage

INDEX(A1:C20, 5, 1)

Syntax

INDEX(reference, [row], [column])

  • reference - The range of cells from which the values are returned.

  • row - [OPTIONAL - 0 by default] - The index of the row to be returned from within the reference range of cells.

  • column - [OPTIONAL - 0 by default] - The index of the column to be returned from within the reference range of cells.

Using INDEX and Match

INDEX and MATCH can be used together to perform more advanced and dynamic lookups.

  • Tip: VLOOKUP can be used when the lookup value is to the left of the desired attribute to return. INDEX and MATCH can be used regardless of where the lookup value is located in the dataset.
Guest Name Dietary Restriction Sent Invitation Table Number
David Vegetarian No 3
Bob None No 5
david None Yes 1
Nancy None No 4
Mary Vegetarian Yes 2

 

Goal Formula Formula Output Comment
Find Mary's Dietary Restriction =VLOOKUP("Mary", A1:D6, 2, false) Vegetarian Both search key and index are hardcoded
Find Mary's Dietary Restriction =INDEX(A1:D6, MATCH("Mary", A1:A6, 0), 2) Vegetarian Row number is dynamic & column number is hardcoded
Find Mary's Dietary Restriction =INDEX(A1:D6, MATCH("Mary", A1:A6, 0), MATCH("Dietary Restriction", A1:D1, 0)) Vegetarian Both row number & column number are dynamic
Find Who is at Table Number 2 Using VLOOKUP, this would not be possible N/A VLOOKUP can only be used when the lookup value is to the left of the desired attribute to return
Find Who is at Table Number 2 =INDEX(A1:D6, MATCH(2, D1:D6, 0), MATCH("Guest Name", A1:D1, 0)) Mary INDEX and MATCH can be used regardless of where the lookup value is located relative to the desired attribute to return

See Also

MATCH: Returns the relative position of an item in a range that matches a specified value.

OFFSET: Returns a range reference shifted a specified number of rows and columns from a starting cell reference.

Notes

  • If you set row or column to 0, INDEX returns the array of values for the entire column or row, respectively.
Was this helpful?
How can we improve it?