Google sheets ArrayFormula with IMPORTRANGE always return same result-CodePudding

I have a Google Sheet that imports column "L" from another file based on the row number

=ArrayFormula(
  IF(LEN(K:K),
    if( ROW(L:L) = 1,
       "Date",
       IMPORTRANGE("https://docs.google.com/spreadsheets/d/123456123456lkjjj","Sheet1!D" & M1:M)
    ),""
  )
)

other file (Sheet1)

The problem always return the first result

CodePudding user response：

Try this:

=ARRAYFORMULA(
  IFS(
    ROW(L:L) = 1,
      "Date",
    K:K = "",,
    True,
      IFNA(VLOOKUP(
        M:M,
        {
            SEQUENCE(ROWS(IMPORTRANGE("https://docs.google.com/spreadsheets/d/123456123456lkjjj", "Sheet1!D:D"))),
            IMPORTRANGE("https://docs.google.com/spreadsheets/d/123456123456lkjjj", "Sheet1!D:D")
        },
        2,
      ))
  )
)

IMPORTRANGE works once inside ARRAYFORMULA, so it is not possible to call it in a loop. But you can get everything with it and then loop through it.