Excel INDEX () Function
Excel INDEX () Function
The INDEX() function in excel returns a value from a list of tables based on the intersection of a row and a column position. This function is used with the MATCH function, where the MATCH locates and feeds a position to INDEX.
Syntax
INDEX (array, row_num, [col_num], [area_num])
Parameter
Array (required)- This parameter represents the range of cells that we need to find the value.
Row_num (required)- This parameter refers to the row position in the reference or array.
Col_num (optional)- This parameter represents the column position in the reference or array. The default value is 1.
Area_num(optional)- This parameter refers to the range in reference that should be used.
Category
Lookup & Reference
Return
This function returns a value from a list of tables based on the intersection of a row and a column position.
Example 1
Objective: Pick value at the intersection of 3rd row and fourth column in a range B71:E82
| ID | Name | Email ID | Salary |
| 650 | Gautam | [email protected] | $936 |
| 764 | Pooja Rani | [email protected] | $1,303 |
| 651 | Aashna Malhotra | [email protected] | $1,378 |
| 774 | Lakshman Maradapa | [email protected] | $1,500 |
| 873 | Ekta Gupta | [email protected] | $1,218 |
| 624 | Nikit Mahiwal | [email protected] | $806 |
| 825 | Meenakshi Jaiswal | [email protected] | $1,007 |
| 818 | Pragati Patel | [email protected] | $1,206 |
| 694 | Rohit Rawat | [email protected] | $1,479 |
| Result | Formula Text |
| 1303 | =INDEX(B71:E82,3,4) |
Example 2
Objective: Pick value at the intersection of 3rd row and fourth column in a range B71:E82
| Emp ID | Purchased Date | Product Category | Product | Unit | Amount |
| E001 | 21-Mar-18 | Camera | ASUS F556UA | 2 | $548 |
| E002 | 22-Mar-19 | Laptop | Canon ES | 2 | $500 |
| E003 | 13-Aug-18 | Watch | Fitbit Blaze Smart Watch | 1 | $73 |
| E004 | 24-Mar-13 | Laptop | Samsung Gear S2 | 4 | $950 |
| E005 | 2-Jun-11 | Watch | Fitbit Blaze Smart Watch | 3 | $198 |
| E006 | 2-Jan-12 | Camera | Pebble Smart Watch | 4 | $306 |
| E007 | 27-Mar-18 | Watch | Apple Watch Sport | 2 | $308 |
| E008 | 28-Mar-15 | Laptop | ASUS F556UA | 3 | $529 |
| E009 | 29-Mar-18 | Watch | HP 15-ay011nr | 2 | $499 |
| E010 | 20-Sep-18 | Laptop | ASUS ROG | 1 | $1,099 |
| Result | Formula Text |
| Canon ES | =INDEX(B71:E82,3,4) |