# MS Excel: INDEX Function (WS)

This Excel tutorial explains how to use the Excel INDEX function with syntax and examples.

## Description

The Microsoft Excel INDEX function returns either the value or the reference to a value from a table or range. There are 2 syntaxes for the INDEX function.

## INDEX Function (Syntax #1 - Returning a value)

The first implementation of the INDEX function returns the value from a table or range. The syntax for the Microsoft Excel INDEX function is:

`INDEX( array, row_number, [column_number] )`

### Parameters or Arguments

array
A range of cells or table.
row_number
The row number in the array to use to return the value.
column_number
Optional. It is the column number in the array to use to return the value.

## Applies To

The INDEX function can be used in the following versions of Microsoft Excel:

• Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

## Type of Excel Function

The INDEX function can be used in Microsoft Excel as the following type of function:

• Worksheet function (WS)

## Example (as Worksheet Function) - Returning a value

Let's look at some Excel INDEX function examples and explore how to use the INDEX function as a worksheet function in Microsoft Excel:

Based on the Excel spreadsheet above, the following INDEX examples would return:

```=INDEX(A1:D5, 2, 3)
Result: \$3.50

=INDEX(A1:D5, 4, 1)
Result: 10569

=INDEX(A1:D5, 5, 2)
Result: 12```

## INDEX Function (Syntax #2 - Returning a Reference to a Value)

The second implementation of the INDEX function returns the reference to a value from a table or range. The syntax for the Microsoft Excel INDEX function is:

`INDEX( reference, row_number, [column_number], [area_number] )`

### Parameters or Arguments

reference
A reference to a range or group of ranges.
row_number
The row number in the range to use to return the reference.
column_number
Optional. It is the column number in the range to use to return the reference.
area_number
Optional. It is the range to use from the reference parameter. If this parameter is omitted, then it will return its results for the first range listed in the reference parameter.

## Applies To

The INDEX function can be used in the following versions of Microsoft Excel:

• Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

## Type of Excel Function

The INDEX function can be used in Microsoft Excel as the following type of function:

• Worksheet function (WS)

## Example (as Worksheet Function) - Returning a Reference to a Value

Let's look at some Excel INDEX function examples and explore how to use the INDEX function as a worksheet function in Microsoft Excel:

Based on the Excel spreadsheet above, the following INDEX examples would return:

```=INDEX(A1:D5,2,3)
Result: reference to cell C2 (which contains the value of \$3.50)

=INDEX((A1:B5,C1:D5),3,2)
Result: reference to cell B3 (which contains the value of 4)

=INDEX((A1:B5,C1:D5),3,2,1)
Result: reference to cell B3 (which contains the value of 4)

=INDEX((A1:B5,C1:D5),3,2,2)
Result: reference to cell D3 (which contains the value of \$20.00)```

## Frequently Asked Questions

Question:I have a question about how to nest a MATCH function within the INDEX function. The question is:

I want to create a formula using the MATCH function nested within the INDEX function to retrieve the Class that was selected (by the x) in E4:F10. The MATCH function should find the row where the x is located and should be used within the INDEX function to retrieve the associated Class value from the same row within F4:F10.

Answer:We can use the MATCH function to find the row position in the range E4:E10 to find the row where "x" is located. We then embed this MATCH function within the INDEX function to return the corresponding value in the range F4:\$10 as follow:

`=INDEX(\$F\$4:\$F\$10, MATCH("x",\$E\$4:\$E\$10))`

In this example, we are searching for the value "x" within the range E4:E10. When the value of "x" is found, it will return the corresponding value from F4:F10.

Share: