Index Formula as name suggests in excel gives the value of the cell from a given position from a range of cells provided, this formula is often used with match function as a substitute of VLookup function, this function has four arguments where array and row number are mandatory while column number and area number are optional arguments.
Excel INDEX Formula
Of all the many invaluable excel formulas INDEX is one of the top-ranked formulae to lookup values. When we say lookup functions first thing will come to our mind is VLOOKUP, but we need to go beyond VLOOKUP to have in-depth knowledge on lookup function in excel.
The Formula of the INDEX function excel has two sets of syntax.
First argument =INDEX (array, row_num, [col_num]) is used in most of the situations.
Second argument =INDEX (array, row_num, [col_num], [area_num]) is very useful in case of three way lookup.
- Array: This is simply the result column in the data table.
- Row Num: From which row of the data table actually you are looking for the result.
- [Column Num]: From which column of the data table you required the data to be fetched.
- [Area Num]: If the argument includes many ranges then this number will help to select the reference from all the ranges.
Ok, let’s discover the examples of using INDEX formula in excel.
What is the INDEX Formula in Excel?
Searching the particular value in the small amount data is not the toughest task but once the data gets bigger we cannot search the values manually, we need to rely on excel functions.
INDEX formula in excel not only finds you the value but also finds the value in the middle of the data table at the intersection of a particular row and column in a given range.
For example, take look at the below data.
From the above table for the year 2013, I want sales value for the region East. In VLOOKUP we need to mention the column index number but using excel INDEX formula we can we can easily get the sales value for the year 2013 and for the region East.
Examples of INDEX Formula in Excel
Below are some of the examples of INDEX Formula in Excel.
Take the look at the simple example of fetching the value based on lookup value. Consider the below data.
From this data, we need to know the sales value for the year 2015.
Step 1: Open INDEX function in E3 cell.
Step 2: Array is nothing but from which column we need the data, in this case, we need a result from B2 to B10.
Step 3: From which row we need the data i.e. the year 2015 row number is 6.
Step 4: Since the data has only one result column, so no need of mentioning the column number. Close the bracket, we would get sales value for the year 2015.
We have seen how to fetch the data in a single array. Now we will see how to fetch the data with multiple arrays.
From the above table, we need sales value for the year 2013 for the region East.
Step 1: Open INDEX function in the cell H2.
Step 2: Array results from column since we have many columns select the data from B2 to E10.
Step 3: Next is from which row we are looking for the data i.e. 4th row.
Step 4: From which column we looking for the result i.e. East column i.e. 3rd column.
Yes, we go the value correctly. We need to mention the row number and column number manually, but how about making this row number column number dynamic?
We will see this in the next example.
In the previous example, we need to enter row number and column number manually. Now we will see how to get row number and column number dynamically.
Consider the same data and problem from the above example.
Step 1: Apply the INDEX and select the result range.
Step 2: Instead of entering the row number manually open MATCH function.
Step 3: Select the lookup value as Year i.e. G2 cell.
Step 4: Lookup array as A2 to A10 range.
Step 5: Match type is the FALSE i.e. exact match.
Now this will return row number for the year 2013.
Step 6: To get the column number open one more MATCH function.
Step 7: Now select the lookup value as East i.e. H1 cell.
Step 8: Lookup array is from B1 to E1.
Step 9: Match type is an exact match, so select zero.
Oh yes, we got the same result as the previous example. Now change the year to 2015 and see.
I have just change the year in the cell G2, formula automatically returned the result. Now change the Region to West.
Like this INDEX + MATCH function can fetch the data at the intersection of the row and column.
Finally, we can use INDEX + MATCH as the alternative to VLOOKUP function in excel.
We need to find Sales value for the year 2015.
Step 1: Open INDEX function in E2 cell.
Step 2: Select the result array as B2 to B10.
Step 3: For row number apply MATCH function.
Like this, we can use INDEX + MATCH as an alternative to VLOOKUP function.
Things to Remember
- MATCH function returns the row and column number for the selected lookup value.
- INDEX + MATCH can fetch the data from both ways i.e. from right to left and from left to right.
- VLOOKUP can only fetch data from left to right.
This has been a guide to INDEX Formula in Excel. Here we discuss the INDEX and Match Formula in Excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –