INDEX function in Excel

INDEX returns the value at a given row and column position inside a range.

INDEX answers a simple question: "what is in the 3rd row of this range?" On its own that looks too plain to be useful. Paired with MATCH, which finds positions, it becomes the most flexible lookup tool in Excel, and one that works in every version.

Syntax

=INDEX(array, row_num, [column_num])

Argument	What it is
`array`	The range to take a value from.
`row_num`	The row inside the range, counted from its top. 1 is the first row.
`column_num`	The column inside the range, counted from its left. Not needed when the range is a single column.

Examples

Each result below is what Excel itself showed for the formula on the sample sheet.

Sample sheet: A small price list with item codes. The table is sorted by code.

	A	B	C	D	E
1	Code	Item	Category	Price	Stock
2	A101	Basmati Rice 5 kg	Grocery	650	40
3	A102	Toor Dal 1 kg	Grocery	160	75
4	B201	Notebook	Stationery	60	300
5	B202	Ball Pen	Stationery	10	0
6	C301	LED Bulb	Electronics	110	200
7	C302	Power Bank	Electronics	1299	18
8	D401	Steel Bottle	Home	450	55

=INDEX(B2:B8,3)
Result: Notebook
The 3rd item in the list of names.
=INDEX(A2:E8,5,4)
Result: 110
Row 5, column 4 of the table: the price of the LED Bulb.
=INDEX(B2:B8,MATCH("C302",A2:A8,0))
Result: Power Bank
The classic pair: MATCH finds the row of code C302, INDEX returns the name on that row.
=INDEX(A2:A8,MATCH("Notebook",B2:B8,0))
Result: B201
A lookup to the left, which VLOOKUP cannot do: the code of the Notebook.
=INDEX(A2:E8,MATCH("D401",A2:A8,0),MATCH("Stock",A1:E1,0))
Result: 55
Two MATCHes: one finds the row, the other finds the Stock column by its heading.
=INDEX(D2:D8,COUNTA(D2:D8))
Result: 450
The last entry in a column: count the filled cells and take that position.

How it works

Give INDEX a range and a position, and it returns the cell at that position. For a single column or a single row, one number is enough. For a table, give the row and then the column.

The numbers count from the corner of the range you selected, not from A1. In =INDEX(C10:C20,2) the answer is cell C11.

INDEX and MATCH together

MATCH tells you where something is. INDEX tells you what is at a place. Join them and you have a lookup:

=INDEX(answer_column, MATCH(what_to_find, search_column, 0))

Read it from the inside. MATCH looks for the value in the search column and returns its position, say 6. INDEX then returns the 6th item of the answer column.

This pair beats VLOOKUP in the same ways XLOOKUP does:

the answer column can be to the left of the search column;
inserting or moving columns does not break the formula;
there is no column number to count by hand.

And unlike XLOOKUP, it works in Excel 2010, 2013, 2016 and 2019. If a workbook will be opened on older computers, this is the safe choice. The guide INDEX and MATCH step by step builds one slowly.

Two-way lookups

With a second MATCH for the column, INDEX picks the cell where a row label and a column heading meet, as the fifth example shows. This is the tidy way to read from a grid such as a rate chart with weights down the side and zones across the top.

Returning a whole row or column

Use 0 for the row number to get an entire column of the range, or 0 for the column number to get an entire row. =SUM(INDEX(A2:E8,0,5)) adds up the whole fifth column. This is handy when the column to total is chosen by a formula.

Common mistakes

#REF! means the position is outside the range, for example row 9 of a range with 7 rows.
Ranges that do not line up. If INDEX looks at B2:B8 and MATCH searches A3:A9, every answer is off by one row. Start both ranges on the same row.
Forgetting the 0 in MATCH. Without it MATCH does an approximate search and may return a wrong position with no error.

In Google Sheets

INDEX works the same way in Google Sheets.

Related functions

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.