# Solved Excel Formula: Using Current Month to locate a value

May 9, 2013 at 13:21:08
Specs: Windows 7
 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.

See More: Excel Formula: Using Current Month to locate a value

#1
May 9, 2013 at 16:14:28
 You say that you want to return "the value that is always in column C" but you don't say where the date (e.g. 5/1/13) will be found.For the purpose of this exercise, I'll assume your dates are in Sheet2!B1:16 and you values are in Sheet2!C1:C16. Modify as required.=INDEX(Sheet2!\$B\$1:\$C\$16,MATCH(DATE(YEAR(TODAY()),MONTH(TODAY()),1),Sheet2!\$B\$1:\$B\$16,0),2)DATE(YEAR(TODAY()),MONTH(TODAY()),1) converts today's date to the first of the current month.MATCH(DATE(YEAR(TODAY()),MONTH(TODAY()),1),Sheet2!\$B\$1:\$B\$16,0) finds the converted date in Sheet2!B1:B16 and returns the relative position. It then uses that relative position as the row_num argument for the INDEX function.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#2
May 10, 2013 at 05:57:30
 Sorry for not being clear. Thanks for the formula, it is pulling data, but the value I want to pull is in column F and the date is in column B. How do I show this in the beginning of your INDEX formula? =INDEX(Sheet2\$B\$1 ?

Report •

#3
May 10, 2013 at 07:26:27