Google-sheets – Pull a value for the following month in Google Sheets

google sheetsgoogle-sheets-datesvlookup

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

Best Answer

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.

Formula

=SORT(ARRAYFORMULA(TEXT(MONTH(FILTER(A2:A;A2:A<>""));"00") & " - " 
 & TEXT(DAY(FILTER(A2:A;A2:A<>""));"00"));1;1)

Explained

Here's a break-down description of the formula:

The FILTER function will filter for all rows that have something.
The MONTH and DAY functions will extract the respective values from the date.
The TEXT function will convert the value from point 2 into a pre-formatted STRING.
The ARRAYFORMULA function will apply all the above to the complete column (skipping the header).
The SORT function will sort the result, given by the ARRAYFORMULA.

The SORT function allows for sorting. If you do that via a column sort, the entry of the ARRAYFORMULA is taken into account as well and gets re-positioned, causing mayhem.

Screenshot

Example

See example file I've created: sorting dates as text

Google-sheets – Make dynamic array blank based on adjacent values in Google Spreadsheets

Direct answer

The following formula return the expected results for the three cases exposed in the question. It should be added to cell A2 and cells in A3:A13 should be empty.

=ArrayFormula(
  IF(
    NOT(
      (ROW(B2:B13)-1=1)
      *
      (B2:B13="$")
    )
    *
    (
      (B2:B13=">")
      +
      (B2:B13="$")
      +
      (ROW(B2:B13)-1>=Vlookup("$",{B2:B12,ROW(B2:B12)},2,0))
    )
    *
    NOT((B1:B12=">")
    *(B2:B13="$")),
    ".",
    " "
  )
)

Explanation

In this case the use of ArrayFormula function together with references to B2:B13 will return an array of 12 rows and 1 columns with the expected values.

The comparison of a range or array with a scalar value returns an array of the same size of the range or array. All the tests should return an array of the same size.

AND and OR functions could not work with arrays, but Google Sheets interpret TRUE as 1 and FALSE as 0, so, instead of using AND function use * and instead of OR use +.

As + and * take precedence over comparison operators each test should be enclosed in parentheses.