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)



ADVERTISEMENT
ADVERTISEMENT