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

#### dbmathis

##### Well-known Member
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
That's the formula I used. And it gave #N/A

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### barry houdini

##### MrExcel MVP
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

##### Well-known Member
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

##### Well-known Member
Is this possibly?

#### barry houdini

##### MrExcel MVP

Didn't the last formula work?

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

#### dbmathis

##### Well-known Member
start = to 10/1/2005

#### dbmathis

##### Well-known Member

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

##### MrExcel MVP
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

##### Well-known Member
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

##### MrExcel MVP
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

Replies
2
Views
195
Replies
3
Views
59
Replies
8
Views
63
Replies
1
Views
75
Replies
1
Views
458

1,128,167
Messages
5,629,079
Members
416,364
Latest member
maatpsr

### 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.

### Which adblocker are you using?

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

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