Search code examples
google-sheetsgoogle-sheets-formula

Adding a condition to a MAP formula


Now the formula looks for the dash sign in column A and outputs the 4th and 6th cell after it (the formula itself is in cell G1). It is necessary that if the word “OT” is found (as in row 30 and 108), the formula would output rows 6 and 8 in addition to the first condition.

MAP(FILTER(row(A:A), A:A = "–"), LAMBDA(_r, {index(A:A, _r + 4), index(A:A, _r + 6)}))

I have made a sample of what should get next to it, the places that need to be fixed highlighted in yellow

https://docs.google.com/spreadsheets/d/13bmHZYMBtRYjT0lEuhq6GU_0ezXPNrIu47TDTbfl8kk/edit?gid=1601673558#gid=1601673558


Solution

  • Here's something you can try. I changed your lambda helper function to reduce instead of map, using vstack:

    =let(z,tocol(,1),range,(FILTER(row(A:A), A:A = "–")),REDUCE(z,range,LAMBDA(a,c,vstack(a,if(index(A:A, c + 4)<>"OT",{index(A:A, c + 4), index(A:A, c + 6)},{index(A:A, c + 6), index(A:A, c + 8)})))))