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]

ok,

That fixed it. Would you mind explaining why in this instance I need {}?

And thanks.

 

[barry houdini]

You need to use CTRL+SHIFT+ENTER whenever you have a formula, or part of a formula that normally produces a single value but you're asking it to produce multiple values.

In this particular case with

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

the MONTH function, when fed a date, gives the month number e.g.

MONTH(TODAY()) = 10 but we're feeding it with multiple dates in

MONTH(G131:R131)

This is the bit that needs CTRL+SHIFT+ENTER so that an array of values is produced, something like {10;11;12;1;2;3;4;5;6;7;8;9}