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)
INDEX Function in Excel: Reference Form (Quick View)
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:
 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.
 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.
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:
 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.
 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.
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 semicolons (;)
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).
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 twodimensional 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.
We can work on twodimensional 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 4^{th} row  Returns the entire 4^{th} row 
=INDEX({1,2,3;4,5,6;7,8,9;10,11,12},,3)

Returns entire 3^{rd} column  Returns the entire 3^{rd} 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.
The formula used in cell C20: =INDEX(sales,MATCH(C18,products,0),MATCH(C19,C8:E8,0))
Explanation of the formula:
 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 4^{th} 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.
 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 3^{rd} position, so this function returns 3. In the same way, using the F9 keyboard shortcut, we can find the returned value is 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 4^{th} row and 3^{rd} column in the cell range sales. So, the final output is: 2634
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.
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.
Now press CTRL + SHIFT + ENTER to enter the formula as an array formula.
In the same way, you can show the Entire Column.
We just calculated the Total Sales Year wise for the above dataset.
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 2^{nd} column from the cell range sales (C9:E14).
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:
 Select cell C20 where the formula lies
 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.
 Now click on the Evaluate The formula is now showing the cell range $C$9:$E$14 instead of the sales named range.
 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.
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.
If you are using other Excel versions, then follow these steps:
 Select 6 rows and 3 columns
 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.
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:
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.
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.
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 3^{rd} row (C11: E11) from the range sales.
 The second index function, INDEX(sales,0,2), returns the 2^{nd} 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.
In the cell C20, I have used this formula: =INDEX(sales,MATCH(C19,products,0),0) INDEX(sales,0,MATCH(C18,C8:E8,0))
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 5^{th} 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 1^{st} item in the list.
 So, the whole formula is now like: =INDEX(sales,5,0) INDEX(sales,0,1)
 Now this formula is selfexplanatory. 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.
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 1^{st} column (C9:C14) from the range sales.
 INDEX(sales,0,3): This part of the formula returns the 3^{rd} 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.
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.
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))
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 3^{rd} row and 2^{nd} 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 nonnumeric 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.
Have tried the links to download the 1200+ macros examples ebook & 100+ excel functions cheat sheet. However, it keeps asking me to reload or reregister. 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
How about adding a link to save the content for later reference?
Ahmed Sheikh
[email protected]
You can do that Ahmed. Thank you.
Regards
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.
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.
Waleed,
Can you upload the working files of your problems? At least a sample file? If possible send an email to this address [email protected]
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?
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