Logo
×

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 gautam.partamy@edu.in $936
764 Pooja Rani poja@edu.in $1,303
651 Aashna Malhotra aasahna.malhotra@edu.in $1,378
774 Lakshman Maradapa lakshman.maradapa@edu.in $1,500
873 Ekta Gupta ekta.gupta@edu.in $1,218
624 Nikit Mahiwal nikit.mahiwal@edu.in $806
825 Meenakshi Jaiswal meenakshi.jaiswal@edu.in $1,007
818 Pragati Patel pragati.patel@edu.in $1,206
694 Rohit Rawat rohit.rawat@edu.in $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)

Related Topics

Unique Values in Excel

The Unique function in Excel filters the list of unique values from the given data like range or array. The data presented is either number, text, date, time etc. This...

3 minutes read.

Excel Unique Values

The Unique function in Excel filters the list of unique values from the given data like range or array. The data presented is either number, text, date, time etc. This...

3 minutes read.

Excel Themes

Microsoft Excel contains various features to enhance the appearance of the worksheet like text color, font size and general outlook of the objects. The users are provided with multiple theme...

3 minutes read.

Excel SEARCH() Function

The SEARCH() function in excel finds one text value within another. It is the same, unlike FIND() function, with the only difference that it in a case-insensitive function. Syntax SEARCH (find_text, within_text, [start_num]) Parameter...

1 minute read.

What do you understand by Combination Chart in Microsoft Excel?

In Microsoft Excel, an individual has the Combo Chart option available, which can be effectively clubbed into two charts types, which are Column Clusters Chart, Line Chart to get the...

7 minutes read.

Row Difference in Excel

Microsoft Excel is used for multiple purposes in various organizations. It is a combination of numeric values, alphabets etc. To calculate data, the user organizes the data according to their...

5 minutes read.

Excel HLOOKUP() Function

Excel HLOOKUP() Function The HLOOKUP() function in excel is used to search a value in the topmost row of a table and then return a corresponding value in the same column for the specified...

2 minutes read.

SUM Functions in Excel

The BODMAS formula plays a vital role in the concept of mathematics. BODMAS rule follows the Bracket of Division, Multiplication, Addition and Subtraction. This rule is followed for a correct...

6 minutes read.

How to print titles in Excel

In Microsoft Excel, if a particular task is completed in the worksheet, the user wants to print the worksheet. While performing this job, the row and column headings will display...

3 minutes read.

Adding Column in Excel

Excel spreadsheet is a combination of rows and columns. After creating the table, there is a need to insert additional row or columns. To organize a better worksheet for calculations,...

5 minutes read.

Excel LEFT() Function

The LEFT () function in excel returns the leftmost characters from a text value. This function is used for data manipulation. Syntax LEFT(Text, [num_chars]) Parameter Text (required)- This parameter represents the string from which you want to...

1 minute read.

Pivots Table in Excel

What is a Pivot? A pivot table is a tool to create summary reports from data-sets irrespective of their sizes but is helpful even if the data is more than just...

5 minutes read.

Excel IFERROR() Function

The IFERROR() function in excel returns the specified value if initially supplied the argument or formula returns an error, otherwise returns the result of the formula supplied in the first argument. When...

2 minutes read.

Notes in Excel

Notes in Excel Notes are used to include additional information to the cell and explain formulas calculation, any modification made to the cell, providing necessary information on data that is entered or to provide any...

3 minutes read.

Excel Tutorial for Beginners

What is Excel? Microsoft Excel is a software program included in the Microsoft Office suite. It is a commercial spreadsheet application developed by Microsoft for Windows, macOS, Android, and iOS. It...

5 minutes read.

Excel DAYS() Function

The DAYS() function in excel returns the number of days that exist between two dates. Syntax DAYS (end_date, start_date) Parameter end_date (required)- This parameter represents the end date. start_date (required)- This parameter represents the start date. Category Date...

1 minute read.

Excel SUBSTITUTE() Function

The SUBSTITUTE() function in excel substitutes/replaces the old text with the new text in a string. SUBSTITUTE function is used to clean data. This function is case-sensitive. Syntax SUBSTITUTE(Text, Old_Text, New_Text, [instance_number]) Parameter Text (required)- This...

2 minutes read.

Multiplication in Excel

Multiplication plays a vital role in Mathematics. Usually, the calculations are performed manually and automatically. Manual calculations take time and sometimes leads to error, and it is a challenging process...

5 minutes read.

Excel MAX() Function

Excel MAX() Function The MAX() function in excel returns the largest or maximum number from a range of cells. It ignores logical values and text. Syntax MAX(Number1,Number2, ...) Parameter Number1(required)- This parameter represents the string for which you...

1 minute read.

Text to columns in Excel

Microsoft Excel is widely used in startups to large companies. The data are entered in required rows and columns, and sometimes the data entered in one cell needs to enter...

4 minutes read.