How to Use INDEX Function in Excel (8 Examples)

The INDEX Function is one of the top used 10 Excel functions. In this tutorial, you will get a complete idea of how the INDEX function works in Excel individually and with other Excel functions.

You will get the Excel INDEX function in two forms: Array Form and Reference Form:

INDEX Function in Excel: Array Form (Quick View)

Excel index function in Array form

INDEX Function in Excel: Reference Form (Quick View)

Excel Index Function in Reference Form

Download Excel Workbook

Download the Excel workbook so that you can practice yourself.

Excel Index Function: Syntax & Arguments

You will get INDEX function in two forms:

  1. The array form: When you intend to return a value (or values) from a single range, you will use the array form of the INDEX function.
  2. And the reference form: when you intend to return a value (or values) from multiple ranges, you will use the reference form of the INDEX function.
Syntax of INDEX function

Syntax of the INDEX function

1. Array form

Summary

Index Function returns a value or reference of the cell at the intersection of a particular row and column, in a given range.

Syntax

Array syntax form: =INDEX (array, row_num, [column_num])

Arguments

Argument Required or Optional Value
array Required Pass a range of cells, or an array constant to this argument
row_num Required Pass the row number in the cell range or the array constant
col_num Optional Pass the column number in the cell range or the array constant

NOTE:

  1. If you use both the row_num and column_num arguments, the INDEX function will return the value from the cell at the intersection of the row_num and column_num.
  2. If you set row_num or column_num to 0 (zero), then you will get the whole column values or the whole row values respectively in the form of arrays. You can insert those values into cells using Array Formula [we shall see it later].

2. Reference Form

Summary

Index Function returns a value or reference of the cell at the intersection of a particular row and column, in a given range.

Syntax

Array syntax form: =INDEX (reference, row_num, [column_num], [area_num])

Arguments

Argument Required or Optional Value
array Required Pass more than one range or array
row_num Required Pass the row number in a specific cell range
col_num Optional Pass the column number in a specific cell range
area_num Optional Pass the area number that you want to select from a group of ranges

Note:

  • If pass more than one range or array as the array value, you should pass also the area_num.
  • If the area_num is absent, the INDEX Function will work with the first range. If you pass a value as the area_num, the INDEX function will work that specific range.

If the concepts are not clear, do not worry; go to the next step, where I am going to show you a good number of examples to use Excel’s Index function effectively.

How to Use the Index Function in Excel (8 Examples)

Example 1: Select an item from a list

Using the Excel Index function, we can retrieve any item from a list. In the following image, you are observing some items are listed under the Product title. Using the Index function, we can retrieve every item from this list.

Return items from a list using Excel Index Function

Based on the above worksheet, Index Functions will return outputs as follows:

Formula Output Explanation
=INDEX(products,1) Windows 11 // Returns the 1st item from the array products
=INDEX(products,2) Windows 10 // Returns the 2nd item from the array products
=INDEX(products,3) Windows 8 // Returns the 3rd item from the array products
=INDEX(products,4) Windows Server // Returns the 4th item from the array products
=INDEX(products,5) Xbox One S // Returns the 5th item from the array products
=INDEX(products,6) Xbox Series X // Returns the 6th item from the array products
=INDEX(products,7) #REF! // Returns #REF error as there is no 7th item in the array products

Example 2: Working on Array Constants Using Index Function

Let’s clear at first what is Array in Excel and how to input an array in Excel:

This is an example of a horizontal array: {45, 20, 15, 89, 65, 100, 18} // Items are separated by commas

How to place this array into Excel:

Select 7 cells horizontally (because the array has 7 items) => Enter the Equal Sign (=) and input the whole above array in the first cell one by one => Press CTRL + SHIFT + ENTER => The array will be placed in all the 7 cells horizontally (image below).

This is an example of a vertical array: {45; 20; 15; 89; 65; 100; 18} // In the vertical array, items are separated by semi-colons (;)

How to place this array into Excel:

Select 7 cells vertically (because the array has 7 items) => Enter the Equal Sign (=) and input the whole above array in the first cell => Press CTRL + SHIFT + ENTER => The array will be placed in all the 7 cells vertically (image below).

Array Constants in Excel

You can directly work on Array Constants using Excel INDEX Function. Check out the following formulas, their outputs and explanations:

Formula Output Explanation
=INDEX({45,20,15,89,65,100,18},1) 45 // 1st item from the array
=INDEX({45,20,15,89,65,100,18},2) 20 // 2nd item from the array
=INDEX({45,20,15,89,65,100,18},3) 15 // 3rd item from the array
=INDEX({45,20,15,89,65,100,18},4) 89 // 4th item from the array
=INDEX({45,20,15,89,65,100,18},5) 65 // 5th item from the array
=INDEX({45,20,15,89,65,100,18},6) 100 // 6th item from the array
=INDEX({45,20,15,89,65,100,18},7) 18 // 7th item from the array
=INDEX({45,20,15,89,65,100,18},8) #REF! // Array has only 7 items

For vertical array constants, you will get the same results:

Formula Output Explanation
=INDEX({45;20;15;89;65;100;18},1) 45 // 1st item from the array
=INDEX({45;20;15;89;65;100;18},2) 20 // 2nd item from the array
=INDEX({45;20;15;89;65;100;18},3) 15 // 3rd item from the array
=INDEX({45;20;15;89;65;100;18},4) 89 // 4th item from the array
=INDEX({45;20;15;89;65;100;18},5) 65 // 5th item from the array
=INDEX({45;20;15;89;65;100;18},6) 100 // 6th item from the array
=INDEX({45;20;15;89;65;100;18},7) 18 // 7th item from the array
=INDEX({45;20;15;89;65;100;18},8) #REF! // Array has only 7 items

Working on two-dimensional array constants:

Example: {1,2,3;4,5,6;7,8,9;10,11,12}

This array has four rows and three columns.

To enter this array into Excel, follow these steps:

Select an Excel range of 3 columns and 4 rows => Enter the Equal Sign (=) and input the whole above array in the first cell => Press CTRL + SHIFT + ENTER => The array will be placed in all the 12 cells horizontally and vertically.

Two dimensional array constants in Excel

We can work on two-dimensional array using Index function. Here are some examples:

Formula Output Explanation
=INDEX({1,2,3;4,5,6;7,8,9;10,11,12},1,1) 1 // Intersection of 1st row and 1st column
=INDEX({1,2,3;4,5,6;7,8,9;10,11,12},1,2) 2 // Intersection of 1st row and 2nd column
=INDEX({1,2,3;4,5,6;7,8,9;10,11,12},1,3) 3 // Intersection of 1st row and 3rd column
=INDEX({1,2,3;4,5,6;7,8,9;10,11,12},2,1) 4 // Intersection of 2nd row and 1st column
=INDEX({1,2,3;4,5,6;7,8,9;10,11,12},2,2) 5 // Intersection of 2nd row and 2nd column
=INDEX({1,2,3;4,5,6;7,8,9;10,11,12},2,3) 6 // Intersection of 2nd row and 3rd column
=INDEX({1,2,3;4,5,6;7,8,9;10,11,12},4,) Returns entire 4th row Returns the entire 4th row
=INDEX({1,2,3;4,5,6;7,8,9;10,11,12},,3)

 

Returns entire 3rd column Returns the entire 3rd column

Example 3: Using INDEX and MATCH Functions Together to Make Selections Dynamic

Using the MATCH function, you can easily retrieve the row and column number for a specific range.

The MATCH function returns the relative position of an item in an array that matches a specified value in a specified order.

=MATCH(lookup_value, lookup_array, [match_type]

In the following image, you are seeing an example of this.

Index Match Function together working

The formula used in cell C20: =INDEX(sales,MATCH(C18,products,0),MATCH(C19,C8:E8,0))

Explanation of the formula:

  1. MATCH(C18,products,0) // In this part of the formula, MATCH function looks for C18 value (Windows Server) in the products range (B9:B14). It is found in the 4th So, this function returns 4. You can see what a function returns using the F9 keyboard shortcut. Select the part of the formula and press the F9 key on the keyboard. In our case, I select MATCH(C18,products,0), and press the F9 keyboard shortcut. The following results I get. Index Match Function
  2. MATCH(C19,C8:E8,0) // In this part of the formula, MATCH function searches for C19 value (2020) in the array C8:E8. It finds it in the 3rd position, so this function returns 3. In the same way, using the F9 keyboard shortcut, we can find the returned value is 3. Index Match Function Example
  3. =INDEX(sales,4,3) // So, our final Excel formula is like this one. This is a very straightforward INDEX formula, returns the Intersection value of the 4th row and 3rd column in the cell range sales. So, the final output is: 2634 Index Function Excel Example

Example 4: Returning the Whole Row or Whole Column from a Cell Range

=INDEX (range, n, 0) // Returns the nth row from the cell range.

=INDEX (range, n, ) // Returns the nth row from the cell range

=INDEX (range, , m) // Returns the mth column from the cell range

=INDEX (range, 0, m) // Returns the mth column from the cell range

For Microsoft 365

If you are using Microsoft 365, then this job is quite easy.

Return whole column or range in excel using Index Function

Other Excel Versions

If you are using old Excel versions than Microsoft 365, then you must use the Array formula to return a row or column from a range using the INDEX Function.

In our image below, every row of sales range (C9:E14) consists of 3 values, so, you must select 3 cells horizontally and then input the INDEX function.

Return the whole column or range in Excel

Now press CTRL + SHIFT + ENTER to enter the formula as an array formula.

Return the whole column or range in Excel using Index function

In the same way, you can show the Entire Column.

We just calculated the Total Sales Year wise for the above dataset.

Index Function Excel Example

Example 5: INDEX Function also Returns Cell Reference

We can return an entire column or a row in a bit different way. And in this process, I will show how the INDEX function can also return a cell reference instead of a cell value.

In the following example, you are seeing I have retrieved the entire 2nd column from the cell range sales (C9:E14).

Index function also returns cell reference

I have used this formula in cell C20: =D9:INDEX(sales,6,2)

I could use this formula =INDEX(sales,0,2) to retrieve the same column but I used the above formula for a purpose.

Let me show you how the INDEX function returns cell reference in the background. Follow these steps:

  1. Select cell C20 where the formula lies
  2. Go to Formulas tab => Formula Auditing group of commands => Click on the Evaluate Formula command. The Evaluate Formula dialog box will open. In the Evaluation field, you will get the formula (=D9:INDEX(sales,6,2)) in the C20 cell reference. Evaluating Excel Formula
  3. Now click on the Evaluate The formula is now showing the cell range $C$9:$E$14 instead of the sales named range. Evaluate Excel Formula
  4. Click again on the Evaluate Now you can see that the whole INDEX function is replaced by cell reference D14. So, the whole INDEX formula has returned a cell reference, not a cell value.Index function returns cell references

Example 6: Returning all the data from a range using Index Function

=INDEX(sales, 0, 0)

What the above formula can return? Can you guess?

In cell C17, I have placed the above formula, all the cell values in the sales range are returned. I am using Microsoft 365, so it happens automatically.

Return whole range using Excel Index Function

If you are using other Excel versions, then follow these steps:

  1. Select 6 rows and 3 columns Return whole range using Index Function
  2. Place the above formula =INDEX(sales, 0, 0) in the first cell and press CTRL + SHIFT + ENTER You will get the sales table as below.

Return whole range with index function

Example 7: INDEX Function can Work with Excel’s Colon, Space, and Comma Operators

Do you know how colon, space, and comma operators work in Excel?

1. Colon (:) Operator:

Examples:

A1:D5 => Refers the range from cell A1 to D5

(A1:C5):(B3:D7) => Refers the range from cell A1 to D7

2. Space ( ) Operator:

The space operator returns the intersection part of two ranges, if there are no intersections between two ranges, it will return an error.

Examples:

(A1:C5) (B3:D7) will return the range B3:C5

3. Comma (,) Operator:

Comma operator works with Excel functions for example SUM, AVERAGE, etc.

Examples:

SUM(A1, B1, C1)

SUM((A:C5),(B3,D7)) // In this case, the intersection part is counted twice in the calculation.

AVERAGE(A1, B1, C1)

To clear your conception, observe the following image:

How colon space and comma operators work in Excel

You can apply the INDEX function with the above operators:

1. Using Index function with Colon (:) Operator

In the following image, you’re seeing we have found Total Sales from the year 2018 to 2019 for the products from Windows 11 to Windows 8.

Colon operator working with Excel Index function

In the cell C24, we have used this formula: =SUM(INDEX(sales,1,1) : INDEX(sales,3,2))

Explanation of the formula:

  • The first INDEX function, INDEX(sales,1,1), returns the cell reference C9 and the second INDEX function, INDEX(sales,3,2), returns the cell reference D11, so the above formula is now: =SUM(C9: D11)
  • Now the SUM function works on the C9:D11 and returns 49283

2. Using INDEX function with Space Operator

In the following example, you see that we have pulled sales data for the Year 2019 and for the product Windows 8.

How space operator works with Excel Index Function

In the cell C20, we have used this formula: =INDEX(sales,3,0)  INDEX(sales,0,2)

Explanation of the formula:

  • The first index function, INDEX(sales,3,0), returns the 3rd row (C11: E11) from the range sales.
  • The second index function, INDEX(sales,0,2), returns the 2nd column (D9: D14) from the range sales.
  • The whole formula is now: =(C11:E11) (D9:D14). The space between these two ranges returns cell D11.

We can also make this formula dynamic using the MATCH function:

In the following image, you see, I have made the whole thing dynamic.

Making Index Function Dynamic using Match Function

In the cell C20, I have used this formula: =INDEX(sales,MATCH(C19,products,0),0)  INDEX(sales,0,MATCH(C18,C8:E8,0))

Space Operator with Excel Index Function

Explanation of the formula:

  • MATCH(C19,products,0): This MATCH function searches for the cell C19 value in the products list, and returns 5 as the cell C19 value (Xbox One S) is the 5th item in the list.
  • MATCH(C18,C8:E8,0): This MATCH function searches for the cell C18 value in the C8:E8 range, and returns 1 as the cell C18 value (2018) is the 1st item in the list.
  • So, the whole formula is now like: =INDEX(sales,5,0) INDEX(sales,0,1)
  • Now this formula is self-explanatory. It returns the intersection of row 5 and column 1 of the range sales. We get the final value as 9739

3. Using INDEX function with comma operator

In the following example, you see that I have calculated the total sales for two years 2018 and 2020 in cell C20.

Comma Operator with Index Function

In the cell C20, I have used this formula: =SUM(INDEX(sales,0,1),INDEX(sales,0,3))

Explanation of the formula:

  • INDEX(sales,0,1): This part of the formula returns the 1st column (C9:C14) from the range sales.
  • INDEX(sales,0,3): This part of the formula returns the 3rd column (E9:E14) from the range sales.
  • Now the formula is: =SUM((C9:C14), (E9:E14))
  • SUM functions sum the ranges C9:C14 and E9:E14.
  • The output is: 129075

We can also make this formula dynamic to sum different columns. For example, this formula will return the same output:

=SUM(INDEX(sales,0,MATCH(C18,C8:E8,0)),INDEX(sales,0,MATCH(C19,C8:E8,0)))

Example 8: Retrieving Data from Different Ranges Using INDEX Function

Sometimes you may have to retrieve data from multiple ranges. In the following image, you’re seeing two ranges: Windows and MS Office.

Excel Index Function Reference Form

You can retrieve any data from these ranges using this version of INDEX function:

=INDEX(reference, row_num, [column_num], [area_num])

Checkout these formulas:

Formula Output Explanation
=INDEX((windows,office),2,3) 8714 // Returns the intersection of 2nd row and 3rd column in the range windows
=INDEX((windows,office),2,3,1) 8714 // Returns the intersection of 2nd row and 3rd column in the range windows
=INDEX((windows,office),2,3,2) 5137 // Returns the intersection of 2nd row and 3rd column in the range office

Using CHOOSE and IF Functions with INDEX to Select a Specific Range

Observe the following image.

You see that I can dynamically select any of the two ranges from Windows and MS Office.

Select between two ranges dynamically

I have used this formula in the cell D23: =INDEX(CHOOSE(IF(D22=”Windows”,1,2),windows,office),MATCH(D20,B9:B12,0),MATCH(D21,C8:F8,0))

Using Choose Function with Index Function

Explanation of the formula:

  • IF(D22=”Windows”,1,2): This part of the formula returns 1 if the cell D22 value is Windows, returns 2 for any other value. As our cell D22 holds value Windows, so, this IF function returns 1. So, our formula becomes now: =INDEX(CHOOSE(1,windows,office),MATCH(D20,B9:B12,0),MATCH(D21,C8:F8,0))
  • CHOOSE(1,windows,office): This part of the formula returns windows range (C9:F12) as CHOOSE function’s index_num argument is 1. So, our formula is now: =INDEX(windows,MATCH(D20,B9:B12,0),MATCH(D21,C8:F8,0))
  • MATCH(D20,B9:B12,0) & MATCH(D21,C8:F8,0): These two parts of the formula returns 3 and 2 respectively. So, our formula is now: =INDEX(windows,3,2)
  • =INDEX(windows,3,2): This is the simplest version of INDEX function. It returns the intersection of 3rd row and 2nd column in the data range windows. Our final output is: 3154

Common Errors While Using INDEX Function

Common Errors When they show
#REF! –          When your passed row_num argument is higher than the existing row numbers in the range

–          When your passed col_num argument is higher than the existing column numbers in the range

–          When your passed area_num argument is higher than the existing area numbers

#VALUE! It occurs when you supply non-numeric values as row_num, col_num, or area_num

Conclusion

INDEX Function is one of the most powerful functions in Excel. To travel through a range of cells, retrieving data from a range of cells, you will use a lot of time Excel’s INDEX Function. If you know a unique way of using Excel’s INDEX Function, let us know in the comment box.

Read More

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

9 Comments
  1. Have tried the links to download the 1200+ macros examples e-book & 100+ excel functions cheat sheet. However, it keeps asking me to reload or re-register. Please if the file sizes are not too large can you forward to my email address.

    • Baber,
      I have sent you an email with instructions. Please check. I hope the email solves the problem.
      Regards

  2. How about adding a link to save the content for later reference?
    Ahmed Sheikh
    [email protected]

  3. I have many data in columns A to E , I want to find all data that corresponding to a specific data from column A . This data from A may be exist three times or more.

  4. I have many data in columns A to E , I want to find all data from column E that corresponding to a specific data from column A. This data from A may be exist three times or more.

  5. I am trying to use the index function to display a dollar amount listed in a table in the month that it will be billed for. I have multiple projects and when the formula is dragged down to the next project, the index gets off because the projects have different start dates. Is there a better way to have the index start at the first billing month other than copying the formula from the previous project to the first billing month of the next project?

  6. Dear All:

    I am looking for a way (without VBA) to create a dynamic array constant which has the value of {1,1,1;2,2,0;3,0,0} in column 3 and {1,1,1,1;2,2,2,0;3,3,0,0;4,0,0,0} in column 4.. and so on and so forth

    This is where I have reached so far:
    I was able to figure out that an array formula =CHOOSE(TRANSPOSE(A1:C1),{1,1,1},{2,2,0},{3,0,0}) where A1=1,B1=2,C1=3 gives me the solution and =A1*–(A1:A3<D1) gives me the value of {1,1,1}.. but when I try combining the above two into a single formula as =CHOOSE(TRANSPOSE(A1:C1),A1:C1*–(OFFSET(A1:C1,0,0,1,C1)<D1)).. i am returned the value of {1,2,3;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!}….

    I cant seem to figure out how to get the above formula to work or some other way to get the constant {1,1,1;2,2,0;3,0,0}.

    Please do help.

    Thanks
    Akshay

Leave a reply

ExcelDemy
Logo