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)