The INDEX function is used when you need to get a value at a specific position in a range or array. You will find this function under the group of LOOKUP and Reference functions. Apart from that, you must have seen a combo of INDEX and MATCH functions.
In this post, you will find simple ways how to use Index on Excel. Other than finding a value in an array, you can use INDEX in other analysis approaches as well. For financial projects, this function is used effectively among other functions.
The INDEX function has two formats:
- Array Format
- Reference Format
INDEX Basic Syntax and Uses:
As already mentioned, the INDEX function has two basic forms and both of them are useful in all editions of Microsoft Excel.
INDEX Array Format
In this format, you will get the value of a particular element in a given range on the basis of rows or columns you set.
INDEX(array, row_num, [column_num])
Let’s understand each argument:
Array: It includes cell range, table, or named range.
Row_num: It includes the number of rows used in an array that gives a value. when there is no row_num, column_num is demanded.
Column_num: It includes the number of columns that gives a value. when there is no column_num, row_num is demanded.
For instance, you will get the value at the intersection of the 4th and 3rd columns by using this formula:
=INDEX(A1:D6, 4, 3)
Here the value you will get is in cell C4.
Below is another example that lets you understand the functionality of the INDEX formula.
A cell reference is a good option rather than using the row and column numbers to get a generic formula:
=INDEX($B$2:$D$6, G2, G1)
Using this formula will let you give a value in terms of item numbers present at the intersection of the product number and week number.
With the help of relative references used in the array arguments, you can simply use absolute references. Or else, you can change a range into a table by pressing CTRL + T.
How to Use INDEX on Excel?
Below is a simple example that explains how easy it is to use the INDEX function:
The following dataset needs to match the location of a value.
The distance that Williams covered has to be found. For this, use this formula:
Below are the outcomes you will get:
Things to Consider in INDEX Array Format:
- The row_num or column_num arguments are not necessarily required to be specified when the array arguments have just one row or column.
- Also note that when the array argument has more than one row, or when the row_num is excluded or set to 0, the INDEX function will give an array of the whole column.
- Likewise, when the array has more than one column or the column_num argument is skipped or set to 0, the INDEX formula will give the whole row.
- You need to ensure that the row_num and column_num arguments must refer to a cell or else the INDEX formula will give the #REF! error.
Things to Remember while Using the INDEX Function:
- You may encounter the #VALUE! error if the given row_num, column_num, or area_num arguments are not in the number form.
- You may encounter the #REF! error if the given row_num is greater than the number of rows, or
- The given col_num is greater than the number of columns, or
- The given area_num is greater than the number of areas in the given range.
Summary:
This is it – Now what?
You are now familiar with how to use INDEX function besides its basic formats and uses. The information given in this post is hopefully useful for Excel users who tend to seek INDEX-relevant data.
