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

dbmathis

Well-known Member
Joined
Sep 22, 2002
Messages
1,064
Hi,

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

I have the following on my sheet.

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

Well-known Member
Joined
Sep 22, 2002
Messages
1,064
ok,

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

And thanks.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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}
 

Watch MrExcel Video

Forum statistics

Threads
1,127,343
Messages
5,624,120
Members
416,012
Latest member
rockermom59

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top