# 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

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### Von Pookie

##### MrExcel MVP
I doubt it's perfect, but this seems to be working for me:

=SUMPRODUCT(--(MONTH(\$G\$131:\$R\$131)=MONTH(TODAY())),--(YEAR(\$G\$131:\$R\$131)=YEAR(TODAY())),\$G\$129:\$R\$129)

#### barry houdini

##### MrExcel MVP
Try

=LOOKUP(MONTH(TODAY()),MONTH(G131:R131),G129:R129)

#### dbmathis

##### Well-known Member
Both of those work initially, but if I rearrange the row and move the moths around the formula doesn't update.

Is this normal? Is there a way to make it update if I re-arrange row G131:R131?

Thanks

#### Von Pookie

##### MrExcel MVP

I can't speak for Barry, but the formula I posted updates correctly when I try it.

Did you perhaps get calculation changed to "manual" instead of "automatic?"

#### dbmathis

##### Well-known Member
I have it set to automatic.

#### dbmathis

##### Well-known Member

This makes no sense! October was in cell R131 and the formula yielded a value of \$6,802.28. I moved October to cell G131 and the formula still says \$6,802.28?

I even closed out the workbook and reopened it and the value is still \$6,802.28. Should the value not be \$977.07 if October is sitting in cell G131?

I am clueless.

#### barry houdini

##### MrExcel MVP
LOOKUP only works when your lookup range (the months in your case) are in ascending order.

If they're not going to be in order (and assuming your dates are the first of each month formatted as "mmm-yy")

=INDEX(G129:R129,MATCH(TODAY()-DAY(TODAY())+1,G131:R131,0))

#### dbmathis

##### Well-known Member
Barry,

I use the format that you specified.

That worked initially, when I had October in cell G131. I got a value of \$977.07.

Then I moved (rearranged the months and put October last in cell R131 and the formula spit out #N/A.

Any ideas?

#### barry houdini

##### MrExcel MVP
Did you see my last post?

What is start equal to?

Replies
2
Views
180
Replies
3
Views
57
Replies
8
Views
57
Replies
1
Views
70
Replies
1
Views
413

1,127,318
Messages
5,623,974
Members
416,002
Latest member
Neshx

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