Search code examples
google-sheetsgoogle-sheets-formulaarray-formulascountinggs-vlookup

How to reference to previous matching record using arrayformula?

I have a project spreadsheet, with project managers entering current progress status for each project for each month where there was some activity. So the columns A B and C are filled, I need to be able to figure out the column D using arrayformula. (note that there was no activity/record for "Project 1" in June 2019)

I thought I would first need a helper column to find the date of the previous record for the project and then vlookup the date for the project and return the progress of that date. But I got stuck when trying to figure out the date for the previous record for the project, but I'm sort of stuck. Maybe someone here can help me with the arrayformula I should enter in D2 in order to get the values D2:D6

https://docs.google.com/spreadsheets/d/1DM8ZK6FwBFAmqUg5bv3o-aTIDtVKKMfGRXc2g9SSYgw/edit?usp=sharing

Solution

  • try:

    =ARRAYFORMULA(IF(A2:A<>"", C2:C-IFNA(VLOOKUP(
 COUNTIFS(B2:B, B2:B, ROW(B2:B), "<="&ROW(B2:B))&B2:B, {
 COUNTIFS(B2:B, B2:B, ROW(B2:B), "<="&ROW(B2:B))+1&B2:B, C2:C}, 2, 0)), ))

    0

    UPDATE:

    =ARRAYFORMULA(IFNA(VLOOKUP(A2:A&B2:B, {SORT(A2:A)&INDEX(SORT(A2:B, 1, 1, 2, 1),,2), 
 IF(A2:A<>"", INDEX(SORT(A2:C, 1, 1, 2, 1),,3)-IFNA(VLOOKUP(
 COUNTIFS(INDEX(SORT(A2:B, 1, 1, 2, 1),,2), INDEX(SORT(A2:B, 1, 1, 2, 1),,2), 
 ROW(B2:B), "<="&ROW(B2:B))&INDEX(SORT(A2:B, 1, 1, 2, 1),,2), {
 COUNTIFS(INDEX(SORT(A2:B, 1, 1, 2, 1),,2), INDEX(SORT(A2:B, 1, 1, 2, 1),,2), 
 ROW(B2:B), "<="&ROW(B2:B))+1&INDEX(SORT(A2:B, 1, 1, 2, 1),,2), 
 INDEX(SORT(A2:C, 1, 1, 2, 1),,3)}, 2, 0)), )}, 2, 0)))

    0

    spreadsheet demo

    UPDATE 2:

    =ARRAYFORMULA(IFNA(VLOOKUP(A2:A&B2:B&C2:C&D2:D&E2:E; {
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;1)&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;2)&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;3)&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;4)&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;5)\ IF(
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;6)<>""; 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;6)-IFNA(VLOOKUP(IF(
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;3)<>""; COUNTIFS(
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;2); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;2); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;3); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;3); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;4); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;4); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;5); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;5); ROW(K2:K); "<="&ROW(K2:K)); )&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;2)&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;3)&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;4)&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;5); {IF(
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;3)<>""; COUNTIFS(
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;2); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;2); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;3); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;3); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;4); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;4); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;5); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;5); ROW(K2:K); "<="&ROW(K2:K))+1; )&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;2)&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;3)&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;4)&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;5)\ 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;6)}; 2; 0)); )}; 2; 0)))

    0

    spreadsheet demo