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
 

Some videos you may like

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
Joined
Feb 17, 2002
Messages
13,686
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)
 

dbmathis

Well-known Member
Joined
Sep 22, 2002
Messages
1,064
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
Joined
Feb 17, 2002
Messages
13,686

ADVERTISEMENT

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
Joined
Sep 22, 2002
Messages
1,064

ADVERTISEMENT

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
Joined
Mar 23, 2005
Messages
20,825
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
Joined
Sep 22, 2002
Messages
1,064
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?
 

Watch MrExcel Video

Forum statistics

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