The Excel Function INDEX Explained
Microsoft Excel offers some very powerful functions for finding values within a data set based on some specified criteria.
Often times, looking for specific data in smaller data sets is a simple task that we can perform visually without the need for a function.
However, this becomes less feasible as we begin to work with larger ranges of data.
“The family of lookup functions provides many ingenious methods to find data in Excel and the function we will be discussing in this article is ‘INDEX’.”
What makes the ‘INDEX’ function so useful and powerful in comparison to some of the other commonly used lookup functions is that it can look up values by both row and column.
In other words, the function will find the value in the cell that is the intersection of the row and column we select.
Not only that, the ‘INDEX’ function also allows us to lookup values from more than one table.
We will take a look at all of this in action below.
All of our examples have been created in Excel 2016 for Windows.
Let’s get started by looking at how ‘INDEX’ looks up a value by row and column.
Basic Syntax: Lookup Based on Row and Column
The first thing you will notice when you type in ‘=INDEX(’ into a cell is that there are two versions or forms of syntax for the function.
Right now, we will look at the first version which is the array form.
The syntax for the array form is:
‘INDEX(array, row_num, [column_num])’
1: The first argument ‘array’, is the range of cells with data that we want to select to look up our data from within.
2: The next parameter, ‘row_num’, is simply the row number of the array from which we want to look up our data.
3: And as you probably have already figured out, ‘[column_num]’ is the column number from which we want to look up our data.
“One thing to note is that whenever we see an argument or parameter within brackets like we see ‘column_num’, this is an indication that it is an optional parameter. More on that in a bit…”
So let’s take a closer look at each of the parameters defined.
In our first example, we will look at a hypothetical scenario where our company can purchase a product in different quantities per box. For each greater quantity per box we purchase, the price is a little less per unit (as indicated by each row).
Also, if we purchase more boxes at one time, we also get a better price as indicated by the columns from left to right.
Follow along by downloading our free sample file right below!
Single Column Array: (column_num optional)
We already noted that ‘array’ is our selected range when using ‘INDEX’.
The first thing we want to look at is a situation where the ‘column_num’ is optional.
This can only be the case when we select an array range that consists of a single column such as B3:B6 (the column for 1 box in our example scenario).
In this case the row number is required.
Practically speaking, we are interested in what our price would be for a box of 500 products if we were to purchase just a single box.
So when we type ‘=INDEX(B3:B6,B9)’ in a cell (C9 in the figure below), our returned value should be $245.00.
Note that in this example we have referenced B9 for our row number which is the value 3. You would get the same result if you replaced B9 in the ‘row_num’ parameter with the actual value 3.
Single Row Array: (row_num optional)
So we just covered the scenario in which we are looking up a value based on the row number in an array with a single column.
Now, let’s take a look at a scenario in which we desire to look up a value based on the column number within a single row.
“Let’s consider another case in which we want to focus on purchasing boxes of products containing 250 per box. We want to look up the price that we will pay if we purchase 10 boxes at one time.”
In this case we will select our array to be the row for 250 items per box which is B4:E4 (the row for 250 in the Qty per Box column).
Similar to how we typed in our row parameter in the previous example, this time we are supplying the column number which in this case is 3.
So our formula should look like ‘=INDEX(B4:E4,3)’.
Again, we are actually using a cell reference in our illustration for column number (see cell B9).
Lookup from an Array of Multiple Rows and Columns
We’ve just stepped our way through understanding the basic idea of the ‘INDEX’ function by focusing arrays consisting of single rows or columns.
Now, it’s time to take the next step to understanding how ‘INDEX’ can allow us to find the intersection of rows and columns in a range of multiple rows and columns.
In this case both parameters for row number and column number will be required.
“Staying with the same example scenario, we now want to look at the entire range B3:E6 and select a row number and a column number at the same time. We are simply combining the previous two example exercises into one task now.”
First, let’s say we would like to know what price we will pay if we purchase 5 boxes of 1000 products.
We simply need get the row and column numbers for each of our parameters.
From a quick glance, we can look at the table of data and see that this will be $467.50.
“Now, let’s imagine we are working with a much larger set of data and that visual inspection of the table is simply not practical.”
We already know what our range is and the row number for 1000 items per box is 4.
We also know that 5 boxes is column number 2 in our array.
Therefore, our formula should be ‘=INDEX(B3:E6,4,2)’
(in our actual example below, the row number and column number are replaced by cell references that contain these values but this is all the same).
Say we wanted to see what our price per box will be if we want to purchase 10 boxes of 500 products per box.
We can simply change our entry into the Qty per Box field (B9) and out Number of Boxes field (B10) to look up the value in the intersection of the row and column for each.
Again, the actual formula contains the cell references that contain the row and column numbers for our look up.
If you replaced the cell references C9 and C10 with the actual number 3 in both, you will get the same result.
“Clearly, ‘INDEX’ can be a very powerful tool that actually provides more flexibility than other lookup functions like the VLOOKUP because it uses both a row and a column reference to locate the look up value.”
Now let’s take a look at the second form of the ‘INDEX’ function.
The reference form of the ‘INDEX’ function allows us to lookup values from multiple ranges rather than just a single range like we have done so far.
For instance, consider a scenario in which you had the following ranges that are exactly in the same form but with different data.
In this case, we have sales numbers for four different music stores.
Each store has its own table of sales across four quarters for various categories of products.
The big difference when using the ‘INDEX’ for reference can probably be best explained by taking a look at the syntax…
Syntax of INDEX for reference:
=INDEX(reference, row_num, [columm_num], [area_num])
Recalling the syntax for the array form of ‘INDEX’, the major difference here is that the ‘array’ parameter is now replaced with a ‘reference’ parameter and there is a new additional parameter, ‘[area number]’.
The ‘reference’ parameter is one or more ranges.
In the above example this would be the four different ranges of sales data for each store.
The ‘[area number]’ parameter is the number position of the range in the reference parameter we want to select. It is optional and if omitted, the first of the ranges in the reference parameter will be selected by default.
“Let’s look at this in action now that we have reviewed the syntax!”
In this example we want to be able to look up the quarterly sales for 4 different product categories that we select for different store locations.
1: The first thing we need to do is make our selections for the reference parameter. This will be four different ranges (our four separate store sales tables) separated by a comma within parentheses.
2: Then the next two parameters are simply the row and column like we have used before.
3: The final parameter, ‘area_num’, is the number position of the four ranges that make up our reference parameter.
For example, if we choose the number 2 for ‘area_num’, this would select the range H4:K7 (our second range in the ‘reference’ parameter).
The following example shows that if we want to find the sales for Store 2 during Q2 for drums, we will choose row number 2, column number 3 and area number 2.
This returns the value $16,300 just as we would expect.
Quick Excel trick to add (a bit) of automation to your ‘INDEX’ look up
Instead of manually inputting the ‘row_num’, ‘column_num’, and ‘area_num’ parameters within the formula every time we want to change these values for our lookup, we can set these parameters to cell references where we change these values as needed.
For instance, we can designate three different cells for:
- Quarter (row_num)
- Category (column_num)
- Location (area_num)
… and place those in our ‘INDEX’ function!
That way, we never have to change the formula and we can simply change the parameters by adjusting the values in the cells have designated for each.
Now that we have covered the concept of the ‘INDEX’ function, you should see how powerful and useful it can be.
As you become more comfortable with using this powerful function, you should begin to see opportunities to combine it with other functions. For example, we’ve written an tutorial on how to use INDEX together with MATCH to achieve some awesome results.
“‘INDEX’ should be one of the most fundamental yet invaluable functions you ever learn to use.”