Forum Discussion
Spill formula with cell reference from lambda map
Hi,
Hopefully someone can give me any advice on the following.
I want to combine a spilled formula array with some logic to determine the label of each cell. That determination is done where the formula looks at the value of the cell itself and the adjacent cell.
In orange I tried to combine the spilled formula but it returns blanks. Formula is as follows:
=LET(tbl;XLOOKUP(A2#&"|"&B1#;tbl_Data[Nummer]&"|"&tbl_Data[PostingDate];tbl_Data[Status];;0);
MAP(tbl;LAMBDA(a;
LET(b;INDIRECT(ADDRESS(ROW(a);COLUMN(a)));
x;AND(OFFSET(b;;0)="Uitgebracht";ISERROR(OFFSET(b;;1)));
y;AND(OFFSET(b;;1)="Opdracht";OFFSET(b;;0)="Uitgebracht");
z;AND(ISERROR(OFFSET(b;;0));ISERROR(OFFSET(b;;1)));
IFERROR(IFS(x;"Vervallen";y;"Nieuw";z;"");"")))))
Anybody any idea what goes wrong?
Many thanks,
Michiel
New functions like DROP, TAKE and EXPAND (as well as HSTACK and VSTACK) can be used in place of OFFSET when working with dynamic arrays. Here's a couple examples, depending on your expected results for the last column of the dataset:
=LET( tbl; XLOOKUP(A2#&"|"&B1#;tbl_Data[Nummer]&"|"&tbl_Data[PostingDate];tbl_Data[Status];""); MAP(DROP(tbl;;-1);DROP(tbl;;1);LAMBDA(a;b;IF(a="Uitgebracht";SWITCH(b;"";"Vervallen";"Opdracht";"Nieuw";"");""))) )Note: tbl can simply reference B2#, but I changed the XLOOKUP formula to return "" if no match is found, so you don't have to deal with the #N/A errors.
=LET( tbl; XLOOKUP(A2#&"|"&B1#;tbl_Data[Nummer]&"|"&tbl_Data[PostingDate];tbl_Data[Status];""); MAP(tbl;EXPAND(DROP(tbl;;1);;COLUMNS(tbl);"");LAMBDA(a;b;IF(a="Uitgebracht";SWITCH(b;"";"Vervallen";"Opdracht";"Nieuw";"");""))) )Alternatively, with PIVOTBY as an all-in-one spill array:
=LET( pvt; PIVOTBY(tbl_Data[Nummer];tbl_Data[PostingDate];tbl_Data[Status];SINGLE;0;0;;0); tbl; DROP(pvt;1;1); arr; MAP(DROP(tbl;;-1);DROP(tbl;;1);LAMBDA(a;b;IF(a="Uitgebracht";SWITCH(b;"";"Vervallen";"Opdracht";"Nieuw";"");""))); HSTACK(EXPAND(pvt;;COLUMNS(pvt)+1;"");VSTACK(EXPAND("";;COLUMNS(arr);"");arr)) )Cheers!
4 Replies
New functions like DROP, TAKE and EXPAND (as well as HSTACK and VSTACK) can be used in place of OFFSET when working with dynamic arrays. Here's a couple examples, depending on your expected results for the last column of the dataset:
=LET( tbl; XLOOKUP(A2#&"|"&B1#;tbl_Data[Nummer]&"|"&tbl_Data[PostingDate];tbl_Data[Status];""); MAP(DROP(tbl;;-1);DROP(tbl;;1);LAMBDA(a;b;IF(a="Uitgebracht";SWITCH(b;"";"Vervallen";"Opdracht";"Nieuw";"");""))) )Note: tbl can simply reference B2#, but I changed the XLOOKUP formula to return "" if no match is found, so you don't have to deal with the #N/A errors.
=LET( tbl; XLOOKUP(A2#&"|"&B1#;tbl_Data[Nummer]&"|"&tbl_Data[PostingDate];tbl_Data[Status];""); MAP(tbl;EXPAND(DROP(tbl;;1);;COLUMNS(tbl);"");LAMBDA(a;b;IF(a="Uitgebracht";SWITCH(b;"";"Vervallen";"Opdracht";"Nieuw";"");""))) )Alternatively, with PIVOTBY as an all-in-one spill array:
=LET( pvt; PIVOTBY(tbl_Data[Nummer];tbl_Data[PostingDate];tbl_Data[Status];SINGLE;0;0;;0); tbl; DROP(pvt;1;1); arr; MAP(DROP(tbl;;-1);DROP(tbl;;1);LAMBDA(a;b;IF(a="Uitgebracht";SWITCH(b;"";"Vervallen";"Opdracht";"Nieuw";"");""))); HSTACK(EXPAND(pvt;;COLUMNS(pvt)+1;"");VSTACK(EXPAND("";;COLUMNS(arr);"");arr)) )Cheers!
Wow great solutions! Many thanks for your input. BR
=LET( tbl, XLOOKUP(A2#&"|"&B1#,tbl_data[Nummer]&"|"&tbl_data[PostingDate],tbl_data[Status],,0), IFERROR( MAKEARRAY( ROWS(tbl),COLUMNS(tbl), LAMBDA(r,c, IFS( AND(INDEX(tbl,r,c)="Uitgebracht",ISNA(INDEX(tbl,r,c+1))), "Vervallen", AND(INDEX(tbl,r,c+1)="Opdracht",INDEX(tbl,r,c)="Uitgebracht"), "Nieuw", TRUE, ""))), "") )I'd apply LAMBDA with MAKEARRAY.
Wow great solutions! Many thanks for your input. BR
