![\"Excel](\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/INDEX-FORMULA-EXCEL.png\")
<\/p>\n<\/p>\n
Excel INDEX\u00a0function can be used when you want to fetch the value from a tabular data and you have the row number and column number of the data point. For example, in the example below, you can use the INDEX function to get the marks of ‘Tom’ in Physics when you know the row number and the column number in the data set.<\/p>\n
![\"Excel](\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-INDEX-Funtion-Example-1.png\")
<\/h5>\nIt returns the value from a table for the specified row number and column number.<\/p>\n
=INDEX (array, row_num, [col_num])
\n=INDEX (array, row_num, [col_num], [area_num])<\/p>\n
INDEX function has 2 syntax. The first one is used in most cases, however, in case of three-way lookups, the second one is used (covered in Example 5).<\/p>\n
Here are six examples of using Excel INDEX Function.<\/p>\n
Suppose you have a dataset as shown below:<\/p>\n
<\/p>\n
To find Tom’s marks in Physics, use the below formula:<\/p>\n
=INDEX($B$3:$E$10,3,2)<\/span><\/pre>\nThis INDEX formula specifies the array as $B$3:$E$10 which has the marks for all the subjects. Then it uses the row number (3) and column number (2) to fetch Tom’s marks in Physics.<\/p>\n
Example 2 –\u00a0Making the LOOKUP Value Dynamic using MATCH Function<\/strong><\/span><\/h3>\nIt may not always be possible to specify the row number and the column number manually. You may have a huge data set, or you may want to make it dynamic so that it automatically identifies the name and\/or subject specified in cells and give the correct result.<\/p>\n
Something as shown below:<\/p>\n
![\"Excel](\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-INDEX-Function-Example-2-Dynamic.gif\")
<\/p>\n
This can be done using a combination of the INDEX and the MATCH<\/a> function.<\/p>\nHere is the formula that will make the lookup values dynamic:<\/p>\n
=INDEX($B$3:$E$10,MATCH($G$5,$A$3:$A$10,0),MATCH($H$4,$B$2:$E$2,0))<\/span><\/pre>\nIn the above formula, instead of hard-coding the row number and the column number, MATCH function is used to make it dynamic.<\/p>\n
\n- Dynamic Row Number is given by the following part of the formula – MATCH($G$5,$A$3:$A$10,0). It scans the name of students and identifies the lookup value ($G$5 in this case). It then returns the row number of the lookup value in the dataset. For example, if the lookup value is Matt, it’ll return 1, if it is Bob, it’ll return 2 and so on.<\/li>\n
- Dynamic Column Number is given by the following part of the formula – MATCH($H$4,$B$2:$E$2,0). It scans the subject names and identifies the lookup value ($H$4 in this case). It then returns the column number of the lookup value in the dataset. For example, if the lookup value is Math, it’ll return 1, if it is Physics, it’ll return 2 and so on.<\/li>\n<\/ul>\n
Example 3\u00a0–\u00a0Using Drop Down Lists as Lookup Values<\/strong><\/span><\/h3>\nIn the above example, we have to manually enter the data. That could be time-consuming and error-prone, especially if you have a huge list of lookup values.<\/p>\n
A good idea in such cases is to create a drop down list of the lookup values (in this case, it could be student names and subjects) and then simply choose from the list. Based on the selection, the formula would automatically update the result.<\/p>\n
Something as shown below:<\/p>\n
![\"Excel](\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-INDEX-Function-Example-3-Dynamic-Drop-Downs.gif\")
<\/p>\n
This makes a good dashboard component as you can have a huge data set with hundreds of students at the back end, but the end user (let\u2019s say a teacher) can quickly get the marks of a student in a subject by simply making the selections from the drop down.<\/p>\n
How to make this:<\/span><\/strong><\/p>\nThe formula used in this case is the same used in Example 2.<\/p>\n
=INDEX($B$3:$E$10,MATCH($G$5,$A$3:$A$10,0),MATCH($H$4,$B$2:$E$2,0))<\/span><\/pre>\nThe lookup values have been converted into drop-down lists.<\/p>\n
Here are the steps to create the Excel drop down list<\/a>:<\/p>\n\n- Select the cell in which you want the drop-down list. In this example, in G4, we want the student names.<\/li>\n
- Go to Data \u2013> Data Tools \u2013> Data Validation.<\/li>\n
- In the Data Validation Dialogue box, within the settings tab, select List from the Allow drop-down.<\/li>\n
- In the source, select $A$3:$A$10<\/li>\n
- Click OK.<\/li>\n<\/ul>\n
Now you\u2019ll have the drop-down list in cell G5. Similarly, you can create one in H4 for the subjects.<\/p>\n
Example 4 – Return Values from an Entire Row\/Column<\/strong><\/span><\/h3>\nIn the above examples, we’ve used Excel INDEX function to do a 2-way lookup and get a single value.<\/p>\n
Now, what if you want to get all the marks of a student. This can enable you to find the maximum\/minimum score of that student, or the total marks scored in all subjects.<\/p>\n
In simple English, you want to first get the entire row of scores for a student (let’s say Bob) and then within those values identify the highest score or the total of all the scores.<\/p>\n
Here is the trick.<\/p>\n
In Excel INDEX Function, when you enter the column number as 0<\/em><\/strong>, it will return the values of that entire row.<\/p>\nSo the formula for this would be:<\/p>\n
=INDEX($B$3:$E$10,MATCH($G$5,$A$3:$A$10,0),0)<\/span><\/pre>\nNow this formula. if used as is, would return the #VALUE! error. While it displays the error, in the backend, it returns an array that has all the scores for Tom –\u00a0{57,77,91,91}.<\/p>\n
If you select the formula in the edit mode and press F9, you’ll be able to see the array it returns (as shown below):\u00a0![\"Excel](\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-INDEX-Function-Example-4-Entire-Row.gif\")
<\/p>\n
Similarly, based on what the lookup value is, when the column number is specified as 0 (or is left blank), it returns all the values in the row for the lookup value<\/p>\n
Now to calculate the total score obtained by Tom, we can simply use the above formula within the SUM function.<\/p>\n
=SUM(INDEX($B$3:$E$10,MATCH($G$5,$A$3:$A$10,0),0))<\/span><\/pre>\nOn similar lines, to calculate the highest score, we can use MAX\/LARGE and to calculate minimum, we can use MIN\/SMALL.<\/p>\n
![\"Excel](\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-INDEX-Function-Example-4-Entire-Row-Highest-SUM.png\")
<\/p>\n
Example 5 –\u00a0Three Way Lookup Using INDEX\/MATCH<\/strong><\/span><\/h3>\nExcel INDEX function is built to handle three-way lookups.<\/p>\n
What is a three-way lookup?<\/p>\n
In the above examples, we’ve\u00a0used one table with scores for students in different subjects. This is an example of a two-way lookup as we use two variables to fetch the score (student’s name and the subject).<\/p>\n
Now,\u00a0suppose in a year, a student has three different levels of exams, Unit Test, Midterm, and Final Examination (that\u2019s what I had when I was a student).<\/p>\n
A three-way lookup would be the ability to get a student\u2019s marks for a specified subject from the specified level of exam. This would make it a three-way lookup as there are three variables (student’s name, subject’s name, and the level of examination).<\/p>\n
Here is an example of a three-way lookup:<\/p>\n
![\"Excel](\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-INDEX-Function-Example-5-Three-Way-Lookup.gif\")
<\/p>\n
In the example above, apart from selecting the student’s name and subject name, you can also select the level of exam. Based on the level of exam, it returns the matching value from one of the three tables.<\/p>\n
Here is the formula used in cell H4:<\/p>\n
=INDEX(($B$3:$E$7,$B$11:$E$15,$B$19:$E$23),MATCH($G$4,$A$3:$A$7,0),MATCH($H$3,$B$2:$E$2,0),IF($H$2=\"Unit Test\",1,IF($H$2=\"Midterm\",2,3)))<\/span><\/pre>\nLet’s break down this formula to understand how it works.<\/p>\n
This formula takes four arguments. INDEX is one of those functions in Excel that has more than one syntax.<\/p>\n
=INDEX (array, row_num, [col_num])<\/span>
\n=INDEX (array, row_num, [col_num], [area_num])<\/span><\/p>\nSo far in all the example above, we have used the first syntax, but to do a three-way lookup, we need to use the second syntax.<\/p>\n
Now let’s see each part of the formula based on the second syntax.<\/p>\n
\n- array –\u00a0($B$3:$E$7,$B$11:$E$15,$B$19:$E$23)<\/span>:\u00a0Instead of using a single array, in this case, we have used three arrays within parenthesis.<\/li>\n
- row_num – MATCH($G$4,$A$3:$A$7,0)<\/span>: MATCH function is used to find the position of the student’s name in cell $G$4 in the list of student’s name.<\/li>\n
- col_num –\u00a0MATCH($H$3,$B$2:$E$2,0):\u00a0MATCH function is used to find the position of the subject name in cell $H$3 in the list of subject’s name.<\/li>\n
- [area_num] – IF($H$2=”Unit Test”,1,IF($H$2=”Midterm”,2,3))<\/span>: The area number value tells the INDEX function which array to select. In this example, we have three arrays in the first argument. If you select Unit Test from the drop-down, the IF function returns 1 and the INDEX functions select 1st array from the three arrays (which is $B$3:$E$7).<\/li>\n<\/ul>\n
Example 6 – Creating a Reference Using the INDEX Function (Dynamic Named Ranges)<\/span><\/h3>\nThis is one wild use of the Excel INDEX function.<\/p>\n
Let’s take a simple example.<\/p>\n
I have a list of names as shown below:<\/p>\n
![\"Excel](\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-Index-Function-Example-6-Reference.png\")
<\/p>\n
Now I can use a simple INDEX function to get the last name on the list.<\/p>\n
Here is the formula:<\/p>\n
=INDEX($A$2:$A$9,COUNTA($A$2:$A$9))<\/span><\/p>\nThis function simply counts the number of cells that are not empty and returns the last item from this list (it works only when there are no blanks in the list).![\"Excel](\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-INDEX-Function-Reference-2.gif\")
<\/p>\n
Now, what here comes the magic.<\/p>\n
If you put the formula in front of a cell reference, the formula would return a cell reference of the matching value (instead of the value itself).<\/p>\n
=A2:INDEX($A$2:$A$9,COUNTA<\/a>($A$2:$A$9))<\/span><\/pre>\nYou would expect the above formula to return =A2:”Josh” (where Josh is the last value in the list). However, it returns =A2:A9 and hence you get an array of names as shown below:![\"Excel](\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-INDEX-Functions-References-3.gif\")
<\/p>\n
One practical example where this technique can be helpful is in creating dynamic named ranges<\/a>.<\/p>\nThat\u2019s it in this tutorial. I\u2019ve tried to cover major examples of using the Excel INDEX function. If you would like to see more examples added to this list, let me know in the comments section.<\/p>\n
Note: I\u2019ve tried my best to proof read this tutorial, but in case you find any errors or spelling mistakes, please let me know \ud83d\ude42<\/p>\n
Excel INDEX Function – Video Tutorial<\/span><\/span><\/h2>\n