Imagine you are monitoring the level of an index or interest rates on a daily basis and data is added in excel on a daily basis. You would like to calculate the year to date return of the data every day. A year to date return is defined as today’s price, interest rate or index level divided by the price, rate or level on the first day of the year. How can we create a dynamic formula so we don’t have to update the formula manually every time? One solution is the use of INDEX() and MATCH function. Let’s see how!
What is an INDEX() function in Excel?
Index(array, row_num, [column_num],[area_num]) returns a value or a reference of the cell at the intersection of a particular row or column in a given range.
How to write the INDEX() function and its arguments?
In the cell where you would like to see the results, type =index(
Choose the array of the data where you would like the cell to be referenced in.
Type in the row and column number as the position of the value in the array. These are basically the x and y location of a data point in a matrix. If you set either of the column or row number to zero, the row or column is omitted and excel will ignore that row or column.
row_num and column_num must point to a cell within the array; otherwise, INDEX returns a #REF! error.
If you decide to use area_num, the array argument would have to be a collection of arrays, an the area_num will determine which array to look at to find the value. In such case, once the formula is completed, we have to press CTRL +SHIFT+ENTER to view the result.
What is an MATCH() function in Excel?
Match(lookup_value, lookup_array,[match_type]) returns the relative position of an item in an array that matches a specified value in specified order.
How to write the Match() function and its arguments?
In the cell where you would like to see the results, type =match(
Type the value or reference the value you would like to find its position in the list or array.
Type the array or reference the list where you would like the lookup_value position to be found.
Choose your match_type from 0, -1, or 1.
A 0 match_type return an exact match.
A -1 match_type returns smallest value that is greater than or equal to the lookup_value. The values in the lookup_array argument must be placed in descending order
A +1 match_type returns the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order in this case.
Let’s see an example as defined in the video.
=(INDEX(F8:F1048576,MATCH(TODAY(),E8:E1048576)))/F8-1