I have a list of values, and they are all for a variety of months (Jan 2018, Feb 2018, &c.). Google Sheets automatically converts these values into 1/1/2018 and so on. Is there a formula I can use to grab the value of the month following today? For example:

Dec 2017 - 10

Jan 2018 - 11

Feb 2018 - 12

Mar 2018 - 13

If I open the sheet today (January 26th, 2018), I want the formula to pull 12 from February. I hope this makes sense. Basically, I'm looking for a Today() code, but for "this month" and then to add one, so it becomes "next month".

  • Asked and answered, but for the sake of the internet(?), Sheets has a dedicated formula for the end of last month, so a formula for this month's 1st could be =EOMONTH(TODAY(),-1)+1 —that is; the last day of last month, plus 1 day. So to get the first day of next month one could use =EDATE(EOMONTH(TODAY(),-1)+1,1). See also the Help Center function reference for TODAY, EOMONTH, EDATE. – Joel Reid Mar 12 at 13:03

Nevermind, I found an answer online after much searching. Here's what the formula looks like on my sheet, if anyone else is interested:

=VLOOKUP(INDEX($A$5:$A$41,MATCH(TRUE,$A$5:$A$41>=TODAY(),0)), A5:B41, 2, FALSE)

There's probably a way to optimize it somehow, but it's working so I'm happy.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.