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

Gauge Chart in Excel

A Gauge Chart in Microsoft Excel is considered the meter type chart of a group dial chart, which is eventually similar to the speedometer that has the pointer towards the...

7 minutes read.

Operators in Excel

In MS Excel, operators are used to defining the operation you want to perform on the used elements or between variables. There are four types of operators: Arithmetic operators, Comparison...

4 minutes read.

How to use Index and Match in Excel?

The Index and Match function is a familiar and frequently used function in Excel. It is one of the most powerful functions and is easy to use. By default, Excel...

6 minutes read.

Square Root Function in Excel

The concept of Mathematical calculations includes a large number of numbers and calculations are based on the numbers present in the data. Likewise, Square Root is a function used to...

3 minutes read.

Excel AND() Function

The AND() function in excel tests the multiple sets of conditions and returns TRUE if all supplied conditions met the criterion, FALSE otherwise. You need to have a minimum of 2 conditions...

2 minutes read.

Shortcut Keys in Excel

Shortcut Keys in Excel Shortcut keys in Excel are used to simplify the work and save time by pressing a few keys.  These keys are used for navigating and working with Excel spreadsheets quickly and easily. Basic Selection:...

9 minutes 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.

Excel INT() Function

Excel INT() Function The INT() function in excel is used to round a number down to the nearest integer. It removes decimal points from any number value & return rounded down number value in...

1 minute read.

Excel MID() Function

The MID() function in excel is used to return a specific number of characters from a text string starting at the position you specify. One can change the start of the string, thus...

1 minute read.

Paste Options in Excel

Excel spreadsheet is a combination of numeric values, alphabets which is used for calculation purposes. While performing the calculations, there is a need to repeat the data for further process....

6 minutes read.

Excel WORKDAY() Function

Excel WORKDAY() Function The WORKDAY() function in excel returns the serial number of the date before or after a specified number of workdays. Syntax WORKDAY (START_DAY, DAYS, [HOLIDAYS]) Parameter START_DAY (required)- This parameter represents the...

1 minute read.

Separate Strings in Excel

How to Separate String in Excel Day by day, Microsoft Excel is increasing for business and personal use. It is a combination of numbers and alphabets based on the data provided....

4 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.

Decimals in Excel

Microsoft Excel is widely used for calculation purposes for various applications. The calculation consists of numbers which include whole numbers, decimals, integers etc. To perform quick calculations, the numbers should...

5 minutes read.

Text to Columns

Text to Columns This Excel feature is used to split the cell content name of one cell into multiple columns based on a delimiter, such as a space or a special character or based...

2 minutes read.

Calculating the Last Day of the Month in Excel

In Microsoft Excel, various functions exist to calculate the date and time from the current date to the past and future. Among multiple tasks in this tutorial, let's see how...

3 minutes read.

Sum Every Nth Row in Microsoft Excel

The main and the critical use of the SUM Function is used to add all the amounts of the numerical data and thus efficiently produce the sum of all at...

3 minutes read.

Excel VALUE() Function

The VALUE() function in excel converts the specific text representing a number (i.e., a number, date, or time format) into a numerical value. Syntax VALUE(Text) Parameter Text(required)- This parameter represents the string that you want...

1 minute read.

Trendline in Excel

A Trendline is a line imposed on a graph to predict the direction of the data. It connects the series of data together to showcase the data best fit. The...

3 minutes read.

Nearest Multiple in Excel

In Excel, the values in Multiplication can be round up, down and to nearest Multiplication number using Excel default function. This makes the calculation to be performed in an easier...

3 minutes read.