Search code examples
arraysgoogle-sheetsgoogle-sheets-formulatransposegoogle-query-language

Merge and update value from different sheet


Good day! I need to merge and update values from status column of STAT sheet sent to status of DATA.

enter image description here


Solution

  • use:

    =ARRAYFORMULA(IFNA(VLOOKUP(A2:A&"", 
     REGEXREPLACE(TRIM(SPLIT(FLATTEN(QUERY(TRANSPOSE(QUERY(QUERY({
     IF(STAT!A2:A="",,STAT!A2:A&"×"), STAT!C2:C&",", ROW(STAT!C2:C)&STAT!C2:C}, 
     "select Col1,max(Col2) 
      where Col1 is not null 
      group by Col1 
      pivot Col3"), 
     "offset 1", 0)),,9^9)), "×")), ",$", ), 2, 0)))
    

    enter image description here