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

Report •


#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
✔ Best Answer
In your OP you said: "...and return the value that is always in column C" so that is what I solved.

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.


Report •

Related Solutions

#4
May 10, 2013 at 08:12:49
Awesome that worked!! Thanks for all the help! Have a great day!!

Report •


Ask Question