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.

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_numargument for the INDEX function.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

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 ?

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_numargument 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_numandcolumn_numarguments arerelative positionswithin the array, not the actual Row or Column number that Excel uses.In other words, Column F is in

relative position5 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 arelative positionwithin thelookup_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.

Awesome that worked!! Thanks for all the help! Have a great day!!

Ask Your Question

Weekly Poll

How long do you think until flying cars or taxis are common?

Discuss in The Lounge

Poll History