google-sheetslambdagoogle-sheets-formula

Auto pulling a value in a formula to the next value in Google Sheets

now the formula in cell C2 is looking for dates from a range of raw data in column A,

=ARRAYFORMULA(TO_DATE(IF(ISDATE_STRICT( MAP(FILTER(ROW(A:A), A:A = "–"), LAMBDA(_r, INDEX(A:A, _r - 7))))=TRUE,MAP(FILTER(ROW(A:A), A:A = "–"), LAMBDA(_r, INDEX(A:A, _r - 7))))))

please advise me how to supplement this formula so that the formula takes the nearest date that is higher, in case "false" is displayed. Next to it in column E, I have manually made a sample of what should be the result. Link on file: https://docs.google.com/spreadsheets/d/1fZ_u2ZkdUCcHkMndAKAsYllLNF8_9EoyIR0_00j-6s0/edit?gid=0#gid=0

Solution

  • You can use SCAN:

    =SCAN(,
   MAP(
    FILTER(ROW(A:A), A:A = "–"),
    LAMBDA(x, INDEX(A:A, x - 7))
   ),
   LAMBDA(a, c, IF(ISDATE(TO_DATE(c)), c, a))
 )