INDEX Function in Excel: Return a Value by Position

The INDEX function in Excel returns the value at a position you choose: the third item in a list, or the cell where row 4 meets column 3 of a table. It is simple on its own and becomes one of the most useful lookup tools when you pair it with MATCH. This guide explains the syntax, shows one-way and two-way examples, and covers the tricks and errors you will meet in real sheets and job tests.

What does the INDEX function do?#

INDEX takes a range and one or two position numbers, and returns the value at that position. Think of it as "go to row 3, column 2 of this block and tell me what's there". The syntax is:

=INDEX(array, row_num, [column_num])

INDEX works in every version of Excel. All the examples below use this sales table in A1:D6:

INDEX with one row or column#

When the range is a single column or a single row, you only need one number. To get the third value from the North column:

=INDEX(B2:B6,3)

The result is 11,800, the March figure. The count starts at B2, the first cell of the range, so B2 is 1, B3 is 2 and B4 is 3. It does not matter that B4 is row 4 of the sheet.

For a single row, the one number counts across. =INDEX(B3:D3,2) returns 10,200, the second value in the February row.

INDEX with a row and a column#

For a table with several rows and columns, give both a row number and a column number. To find South sales in April:

=INDEX(A2:D6,4,3)

Row 4 of A2:D6 is April, and column 3 is South, so the result is 10,800. Count the columns from the first column of the range: Month is 1, North is 2, South is 3.

Excel counts the same way in every lookup function, from the top-left cell of the range you select. Getting this count wrong by one is the most common INDEX mistake.

How to use INDEX with MATCH#

Typing position numbers by hand is fine for one-off checks, but real lookups need the position found for you. That is what MATCH does: it returns the position of a value in a list.

=INDEX(B2:D6,MATCH("Apr",A2:A6,0),MATCH("West",B1:D1,0))

MATCH("Apr",A2:A6,0) returns 4, and MATCH("West",B1:D1,0) returns 3. INDEX then returns the value in row 4, column 3 of B2:D6, which is 10,400. The 0 in MATCH means exact match.

Notice that the INDEX range starts in column B, so the column MATCH must search B1:D1, starting in the same column. The full method is in the INDEX MATCH beginner's guide. It can also look up with several conditions at once, as shown in lookups with multiple criteria.

Return a whole column or row with 0#

A row number of 0 tells INDEX to return every row, which means the whole column. A column number of 0 returns the whole row. This is handy inside SUM or AVERAGE:

=SUM(INDEX(B2:D6,0,2))

This returns the whole second column, South, and adds it up: 9,500 + 10,200 + 11,000 + 10,800 + 11,900 = 53,400. Replace the 2 with a MATCH on the header and the same formula totals whichever region you pick.

In Microsoft 365 and Excel 2021, =INDEX(B2:D6,0,2) on its own spills the five values into the cells below. In older versions it shows only one value, so wrap it in a function like SUM.

INDEX can also build a range#

INDEX returns a cell reference, not just a value, so you can use it as one end of a range. To total sales from January up to March:

=SUM(B2:INDEX(B2:B6,3))

INDEX(B2:B6,3) points at B4, so the formula becomes SUM(B2:B4): 12,000 + 13,500 + 11,800 = 37,300. Change 3 to a cell where the user types a month number and you have a running year-to-date total.

The same idea finds the last value in a list with no gaps: =INDEX(B2:B6,COUNTA(B2:B6)) returns 15,100, the May figure, and keeps working as you add months.

Why does INDEX return #REF!?#

INDEX returns #REF! when you ask for a position outside the range. In the table above:

If the numbers come from MATCH, a #N/A usually means MATCH couldn't find the value, so INDEX never got a position. The guide on INDEX MATCH not working walks through those cases.

INDEX compared with VLOOKUP and XLOOKUP#

INDEX on its own needs a position, while VLOOKUP and XLOOKUP search for a value. With MATCH added, INDEX can look in any direction and doesn't break when someone inserts a column. That is why many analysts prefer it. For a direct comparison, see VLOOKUP vs INDEX MATCH and INDEX MATCH vs XLOOKUP. The INDEX function guide has a quick syntax reference.

Practise it#

INDEX is easiest to learn by counting positions on a real table. Practise INDEX and MATCH in a live spreadsheet: you type the formula, it checks the position and the result instantly, and then you build the full two-way lookup. Or start with the exercise on this page.