With this data, try your hands on more examples of the INDEX function to gain some experience. You can download the example file below and practice with.
The main reason I brought these formulas is for you to see the different ways you can refer to ranges when dealing with the INDEX function.
See the second row, for instance, I only specify the array and the row number. I still got a result even though I didn’t specify the column number. This is because the range is one dimensional, meaning it has only one column. Even if you don’t bring the column number, excel can still work with that since it got no options to choose from.
Now let’s look at the other version of the INDEX – Reference form.
Index function syntax (Reference Form)
The array form of the INDEX function has been explained into considerable details. Now let’s focus our attention on the other form of the function – the reference form.
Syntax: INDEX(reference, row_num, [column_num], [area-num])
One thing you should take note is the key difference between the two forms which lies in the choice of arguments for the function. While the Array form of the INDEX works with a single table, the reference form can work with multiple tables. Therefore, you don’t need to use the Reference form of the INDEX function if you are referring to only one table in the formula.
Let’s get familiar with the arguments of this form of INDEX.
- reference: This represents a reference to one or more cell ranges.
- Row_num: This argument specifies the number of a row in a reference (or range of cells). If each range in the reference contains only one row, the row_num argument is optional.
- [Column_num]: Specifies the number of a column in a reference (or range of cells). If each range in the reference contains only one column, then the column_num argument is optional.
- [area_num]: This argument specifies one of the ranges in reference from which to return the intersection cell of a specified row and column. Since the Reference form of the INDEX function works with multiple tables, the [area_num] argument is used to select the table to use. 1 is used to select the first area (or table/range), 2 for the second area (or table/range), and so on.
INDEX() function example 2 (Reference form)
The above picture shows two tables with different data. Our goal here is to use both of these tables as references inside the INDEX function.
Therefore, imagine you want to write a formula that will refer to these two tables and retrieve a value from the second table.
Let’s take, for example, quantity for Daniel is in table two. Daniel is in the 7th row (excluding the header row) whilst quantity is in the 4th column. We then need the formula to retrieve the intersection cell of row 7 and column 4.
Below is the formula:
INDEX Formula breakdown
I mentioned before that the main difference between the Array form and Reference form of the INDEX function lies in the arguments. If you have only one table to deal with, go with the array form, otherwise, the reference form is what you need.
Here’s the case we have two tables and want to include both in the INDEX formula. Let’s see how you can do that.
1st argument: Reference
This argument is what differentiates between the two forms of INDEX. In the reference version, this argument can refer to more than one table.
To refer to more ranges (or tables) than one, surround the ranges with parenthesis and separate them with a comma.
2nd and 3rd arguments
The 2nd and 3rd arguments determine the row and column numbers respectively. These arguments, in particular, doesn’t change at all as compared to the Array form of the INDEX function. The 7 and 4 specify the row and column numbers respectively. The new thing here is the last argument – [area_num].
Final argument: [area_num]
What the area number argument does is very simple. It specifies which range in the reference to use. Since we are referring to multiple tables in the reference, we need to specify the table we want to work with. An argument of 1 will refer to the first table, argument of 2 to the second table, and so on.
Writing formulas that include values or text is not a good practice at all. If an argument requires a value or a text, whenever you wish to change this argument, you’ll be forced to crack open the formula for editing.
This is not a problem when you have only one or a few formulas on the line. It only becomes a big problem when you are working with countless formulas.
To avoid these problems in your formulas, always try and avoid using values and use cell references instead.
In other words, if your formula is going to need an argument which is a value (say 2), don’t write the value (2) into the formula. Instead, write the value (2) into another cell and then refer to that cell in the formula. This way, whenever you want to change this particular argument to 4, all you need to do is change the value in that cell.
Let’s try this idea with the first example we worked on.
As seen in the above example, instead of using 5 for the row number argument and 2 for the column number argument, the formula referred to cells C2 and C3 which contain the values to be used for the arguments.
Assuming you want to change the row number or column number, you don’t have to edit the formula to do so. Changing the content in Cells C2 and C3 is all it’ll take.
As seen in the above example, as you edit the content in cells C2 and C3, the result for the INDEX function also changes.