I would like to have a formula that will take the current month you open the spreadsheet and locate that month and year on another tab in the same workbook and return the value that is on that row in the tab.
For Example. Today is May 9 , 2013. In cell A1, I want excel to find 5/1/13 in another tab and return the value that is always in column C, but the cell number will change based on the month.
Excel Formula: Using Current Month To Locate A Value
Share
To pull values from Column F, just expand the array for the INDEX function and change the column_num argument to match the relative position of Column F within the array:
=INDEX(Sheet2!$B$1:$F$16,MATCH(DATE(YEAR(TODAY()),MONTH(TODAY()),1),Sheet2!$B$1:$B$16,0),5)
Note: The row_num and column_num arguments are relative positions within the array, not the actual Row or Column number that Excel uses.
In other words, Column F is in relative position 5 within the array of columns B:F. e.g. B is 1, C is 2, F is 5. The same is true for the MATCH function. It returns a relative position within the lookup_array, not an actual Excel Row or Column number.
If the array starts in Row 1 and/or Column A, then the relative position will be the same as the Row and/or Column number, but if the array starts anywhere else, the numbers won’t match.
Click Here Before Posting Data or VBA Code —> How To Post Data or Code.