Forum Discussion

James_Buist's avatar
James_Buist
Brass Contributor
Oct 11, 2024
Solved

Another Spilled Array connundrum. Variant of a previous discussion that was solved.

Follow on from https://techcommunity.microsoft.com/t5/excel/another-dynamic-spilled-array-challenge-multiply-2-spilled/m-p/4263784   In this situation, I am trying to extend the concept of a previ...
excel
Formulas and Functions
  • djclements's avatar
    djclements
    Oct 13, 2024

    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's avatar
James_Buist
Brass Contributor
Oct 12, 2024
Had a cursory look. Clever. Have a long flight early tomorrow and biz trip to US for a few days. i hope to have proper look sometime next week and look to incorporate into the model.

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
djclements's avatar
djclements
Silver Contributor
Oct 13, 2024

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's avatar
    James_Buist
    Brass Contributor
    Oct 24, 2024

    djclements 

    Really clean. I can use either variant as both the final 2 work well. 

Resources