Forum Discussion
Another Spilled Array connundrum. Variant of a previous discussion that was solved.
James_Buist Sure, if there's potentially other data in between the headcount sections, you could just VSTACK the applicable data instead of using SCAN:
=LET( lookup_val, B15:B21 & "|" & F15:F21, lookup_arr, VSTACK(B4 & "|" & F5:F6, B7 & "|" & F8:F9, B10 & "|" & F11:F12), return_arr, VSTACK(I5:M6, I8:M9, I11:M12), factor, E15:E21, values, I15:M21, IFNA(INDEX(return_arr,XMATCH(lookup_val,lookup_arr),SEQUENCE(,COLUMNS(return_arr))),1)*values*factor )It's also possible to use the secondary syntax of INDEX and make use of the area_num argument; however, it will depend on the consistency of your headcount data (each area must have the same "entities" appearing in the same order, and all "codes" aside from 1 must be included):
=LET( lookup_val, F15:F21, lookup_arr, F5:F6, codes, B15:B21, driver, D15:D21, factor, E15:E21, values, I15:M21, IF(driver="Fixed",1,IFERROR(INDEX((I5:M6,I8:M9,I11:M12),XMATCH(lookup_val,lookup_arr),SEQUENCE(,COLUMNS(values)),codes-1),0))*values*factor )It's cleaner, but also easier to make a mistake if you have many entities/codes. 😉
James_Buist Building off the previous revision, it could be something like this:
=IF(D15:D21="Fixed",1,IFNA(INDEX(I4:M12,XMATCH(B15:B21&"|"&F15:F21,SCAN("",B4:B12,LAMBDA(a,v,IF(v="",a,v)))&"|"&F4:F12),SEQUENCE(,COLUMNS(I4:M12))),0))*I15:M21*E15:E21
Or, for improved readability:
=LET(
lookup_val, B15:B21 & "|" & F15:F21,
lookup_arr, SCAN("",B4:B12,LAMBDA(a,v,IF(v="",a,v))) & "|" & F4:F12,
return_arr, I4:M12,
driver, D15:D21,
factor, E15:E21,
values, I15:M21,
IF(driver="Fixed",1,INDEX(return_arr,XMATCH(lookup_val,lookup_arr),SEQUENCE(,COLUMNS(return_arr))))*values*factor
)
Also, since the "factor" now shows 100% for the "fixed" items, you might be able to omit the "driver" and remove the IF statement altogether:
=LET(
lookup_val, B15:B21 & "|" & F15:F21,
lookup_arr, SCAN("",B4:B12,LAMBDA(a,v,IF(v="",a,v))) & "|" & F4:F12,
return_arr, I4:M12,
factor, E15:E21,
values, I15:M21,
IFNA(INDEX(return_arr,XMATCH(lookup_val,lookup_arr),SEQUENCE(,COLUMNS(return_arr))),1)*values*factor
)
However, I'm not sure if there would be other instances in your actual dataset where no match is found, so this "catch-all" approach to handle #N/A errors might not be what you want. I've put both methods in the attached file. Try them and see...
One comment though, If I have a number of sections in the return_arr, then perhaps it would be better just to stack these into a single array and use that rather than the actual range which may have other stuff added between. So getting a cleaner return_arr with just the data sections stacked.
I'll have a go when I get test implementation
James_Buist Sure, if there's potentially other data in between the headcount sections, you could just VSTACK the applicable data instead of using SCAN:
=LET( lookup_val, B15:B21 & "|" & F15:F21, lookup_arr, VSTACK(B4 & "|" & F5:F6, B7 & "|" & F8:F9, B10 & "|" & F11:F12), return_arr, VSTACK(I5:M6, I8:M9, I11:M12), factor, E15:E21, values, I15:M21, IFNA(INDEX(return_arr,XMATCH(lookup_val,lookup_arr),SEQUENCE(,COLUMNS(return_arr))),1)*values*factor )It's also possible to use the secondary syntax of INDEX and make use of the area_num argument; however, it will depend on the consistency of your headcount data (each area must have the same "entities" appearing in the same order, and all "codes" aside from 1 must be included):
=LET( lookup_val, F15:F21, lookup_arr, F5:F6, codes, B15:B21, driver, D15:D21, factor, E15:E21, values, I15:M21, IF(driver="Fixed",1,IFERROR(INDEX((I5:M6,I8:M9,I11:M12),XMATCH(lookup_val,lookup_arr),SEQUENCE(,COLUMNS(values)),codes-1),0))*values*factor )It's cleaner, but also easier to make a mistake if you have many entities/codes. 😉
Really clean. I can use either variant as both the final 2 work well.
