Excel INDEX function
- Example 1 - Return values based on given position from a range of cells
- Example 2 – Return values based on given position from an array of constants
Description
The INDEX function returns the displayed value based on a given position from a range or an array.
Syntax and Arguments
Formula syntax
| =INDEX (array, row_num, [column_num], [area_num]) |
Arguments
|
Return Value
The INDEX function returns a value by given position from a range of cells.
Usage and Examples
Here some examples are provided to explain how to use the INDEX function.
Example 1 – Return values based on given position from a range of cells
| Description | Result | Formula |
| Value in the second column in range A2:C2 | Returns value in B2 | =INDEX(A2:C2,2) |
| Value in the third row in range A2:A5 | Returns value in A4 | =INDEX(A2:A5,3) |
| Value in the intersection of the third row and second row at range A2:C5 | Returns value in B5 | =INDEX(A2:C5,3,2) |
Example 2 – Return values based on given position from an array of constants
Formula1:
=INDEX({1,2,3;"a","b","c"},2,3)
Explain:
{1,2,3;"a","b","c"}: an array with two rows and three columns, 1, 2, 3 are in the three cells of the first row, and a, b, c, are in cells of the second row.
2,3: return the value in intersection of the second row and the third column.
Return: c
Formula2:
Select a column of cells and type
={INDEX({1,2,3;"a","b","c"},0,3)}
Press Shift + Ctrl + Enter keys at the same time
Explain:
{1,2,3;"a","b","c"}: an array with two rows and three columns, 1, 2, 3 are in cells of the first row, a, b, c are in cells of the second row.
0,3: list all values in the third column.
Return: 3,c
Formula3:
Select a row of cells and type
={INDEX({1,2,3;"a","b","c"},2)}
Press Shift + Ctrl + Enter keys at the same time
Explain:
{1,2,3;"a","b","c"}: an array with two rows and three columns, 1, 2, 3 are in cells of the first row, a, b, c are in cells of the second row.
2: list all values in the second row.
Return: a,b,c,c
Sample File
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.