Formula to find date in row = current date and then cal.

[dbmathis]

Hi,

I have a question for the formula gurus of the forum.

I have the following on my sheet.

Cash Flow (Oct-2005 - Oct-2006).xls
	G	H	I	J	K	L	M	N	O	P	Q	R
129	$977.07	$161.18	$635.29	$2,059.40	$2,533.51	$3,007.62	$3,481.73	$3,955.84	$4,429.95	$5,854.06	$6,328.17	$6,802.28
130	foobared
131	Oct-05	Nov-05	Dec-05	Jan-06	Feb-06	Mar-06	Apr-06	May-06	Jun-06	Jul-06	Aug-06	Sep-06
Cash Flow Statement

How would I construct a formula that would look at row G131:R131 and find the date (month) that matches the current date (month). Then become the value of the cell that is 2 cells above the cell that it found which is the current month?

For example: today the formula would be = 977.07. The formula would see that it is October and choose cell G131 because it is currently Oct and then look 2 cells up and find 977.07.

Thanks

 

[dbmathis]

That's the formula I used. And it gave #N/A

 

[barry houdini]

that formula should work if your dates are 1st of the month formatted as "mmm-yy".

If they're not and you only have one of each month (i.e. you don't care about the year) then the following array formula, confirmed with CTRL+SHIFT+ENTER should work for you


=INDEX(G129:R129,MATCH(MONTH(TODAY()),MONTH(G131:R131),0))

 

[dbmathis]

I use the format that you specify but I get the dates with the following formula:

=EDATE(start,0)

then

=EDATE(start,1)

and so on.

The months are never repeated.

 

[dbmathis]

Is this possibly?

 

[barry houdini]

Didn't the last formula work?

what's start is that equal to TODAY()?

 

[dbmathis]

start = to 10/1/2005

 

[dbmathis]

And with your last formula all I get is #N/A

With you forst formula, it works until I change start to 11/1/2005, then I get #N/A

 

[barry houdini]

If you have start as 1st November 2005 then your october date will be Oct-06 right?

Do you still want to pick up the october value if it's next year's?

either way this should work as long as there is an october date (any year) in the G131:R131 range it should give the corresponding value of row 129

=INDEX(G129:R129,MATCH(MONTH(TODAY()),MONTH(G131:R131),0))

have you confirmed with CTRL+SHIFT+ENTER?

 

[dbmathis]

Yes that would make the Oct date 06. I just want a formula that looks to see in the month in the column is = to the current month. The year should be ignored completely.

What do you mean by CTRL+SHIFT+ENTER?

All I get is a #N/A using that formula?

 

[barry houdini]

If you already have the formula in a cell click somewhere in the formula in the formula bar and then hold down CTRL and SHIFT buttons while hitting ENTER.

This puts curly braces around the formula in the formula bar

normally if you haven't done this you get a #VALUE! error not #N/A, please check that you're using the last formula I quoted above