Exceljet

Quick, clean, and to the point

Excel INDEX Function

Excel INDEX function
Summary 
The Excel INDEX function returns the value at a given position in a range or array. You can use index to retrieve individual values or entire rows and columns. INDEX is often used with the MATCH function, where MATCH locates and feeds a position to INDEX.
Purpose 
Get a value in a list or table based on location
Return value 
The value at a given location.
Syntax 
=INDEX (array, row_num, [col_num], [area_num])
Arguments 
  • array - A range of cells, or an array constant.
  • row_num - The row position in the reference or array.
  • col_num - [optional] The column position in the reference or array.
  • area_num - [optional] The range in reference that should be used.
Usage notes 

Use the INDEX function to get a value from a list or table based on its location. For example, the formula =INDEX(A1:B5,2,2) will return the value at the address B2.

The INDEX function has two forms: array and reference.

Array form

In the array form of INDEX, the first parameter is array, which is supplied as a range of cells or an array constant. The syntax for the array form of INDEX is:

INDEX (array, row_num, [col_num])
  • If both row_num and col_num are supplied, INDEX returns the value in the cell at the intersection of row_num and col_num.
  • If row_num is set to zero, INDEX returns an array of values for the entire row. To use these array values, you can enter the INDEX function as an array formula in horizontal range, or feed the array into another function.
  • If col_num is set to zero, INDEX returns an array of values for the entire column. To use these array values, you can enter the INDEX function as an array formula in vertical range, or feed the array into another function.

Reference form

In the array form of INDEX, the first parameter is reference, which is supplied as a reference to one or more cell ranges. The syntax for the reference form of INDEX is:

INDEX (reference, row_num, [col_num], [area_num])
  • The reference form of INDEX returns the reference of the cell at the intersection row_num and col_num.
  • If reference is supplied as multiple ranges,  area_num indicates which range to use.
  • area_sum is supplied as a number. For example, in the formula =INDEX((A1:C5,A7:C10),2,2,2), area_num is supplied as 2, which refers to the range A7:C10.

Excel Formula Training

Formulas are the key to getting work done in Excel. In this step-by-step training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Start building valuable skills with Excel formulas today. Learn more.

Knowing your site is the best thing that ever happened to me since I started using excel. - MJM
Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course