Forum Discussion

matt0020190's avatar
matt0020190
Brass Contributor
Feb 10, 2025
Solved

Two way sum lookup (by month & name)

Hi all   I am trying to perform a two way lookup from a source table.  Basically I have names of colleagues with a number of days per week recorded. I want to then in another table using a formul...
excel
Formulas and Functions
  • djclements's avatar
    djclements
    Feb 11, 2025

    Another option for modern Excel:

    =LET(
    rϑws, MAP(A12:A15, LAMBDA(v, LET(x, XLOOKUP(v, A4:A7, B4:W7, B2:W2), LAMBDA(x)))),
    cϑls, MAP(B11:G11, EOMONTH(+B11:G11, 0), LAMBDA(s,e, LAMBDA(n, CHOOSE(n, s, e)))),
    MAP(IFNA(rϑws, cϑls), IFNA(cϑls, rϑws), LAMBDA(r,c, SUMIFS(r(), B3:W3, ">=" & c(1), B3:W3, "<=" & c(2))))
)

    This method uses the concept of "thunks" to store the applicable row of data for each name, as well as the applicable start and end dates for each period, inside a separate LAMBDA function. The two resulting vectors of "thunks" are then broadcast both across and down to fill an array for the entire output grid, and MAP loops through both arrays together, applying SUMIFS to each applicable row for each applicable period.

    Alternatively, GROUPBY could also be used, but the order of names will not be exactly the same as the desired output table shown in your sample file:

    =LET(
    tbl, TRANSPOSE(A3:W7),
    mth, IFERROR(EOMONTH(TAKE(tbl,, 1), 0), "Name"),
    arr, TRANSPOSE(GROUPBY(mth, DROP(tbl,, 1), SUM, 3, 0)),
    VSTACK(TEXT(TAKE(arr, 1), "mmm-yy"), DROP(arr, 1))
)

    Cheers!

djclements's avatar
djclements
Bronze Contributor
Feb 11, 2025

Another option for modern Excel:

=LET(
    rϑws, MAP(A12:A15, LAMBDA(v, LET(x, XLOOKUP(v, A4:A7, B4:W7, B2:W2), LAMBDA(x)))),
    cϑls, MAP(B11:G11, EOMONTH(+B11:G11, 0), LAMBDA(s,e, LAMBDA(n, CHOOSE(n, s, e)))),
    MAP(IFNA(rϑws, cϑls), IFNA(cϑls, rϑws), LAMBDA(r,c, SUMIFS(r(), B3:W3, ">=" & c(1), B3:W3, "<=" & c(2))))
)

This method uses the concept of "thunks" to store the applicable row of data for each name, as well as the applicable start and end dates for each period, inside a separate LAMBDA function. The two resulting vectors of "thunks" are then broadcast both across and down to fill an array for the entire output grid, and MAP loops through both arrays together, applying SUMIFS to each applicable row for each applicable period.

Alternatively, GROUPBY could also be used, but the order of names will not be exactly the same as the desired output table shown in your sample file:

=LET(
    tbl, TRANSPOSE(A3:W7),
    mth, IFERROR(EOMONTH(TAKE(tbl,, 1), 0), "Name"),
    arr, TRANSPOSE(GROUPBY(mth, DROP(tbl,, 1), SUM, 3, 0)),
    VSTACK(TEXT(TAKE(arr, 1), "mmm-yy"), DROP(arr, 1))
)

Cheers!

  • djclements's avatar
    djclements
    Bronze Contributor
    Feb 11, 2025

    One more dynamic array option, using MMULT:

    =MMULT(--(A12:A15=TOROW(A4:A7)),MMULT(--B4:W7,--(EOMONTH(+B11:G11,0)=TOCOL(EOMONTH(+B3:W3,0)))))

    Plus, an improved variation of my previous GROUPBY suggestion:

    =LET(
    tbl, TRANSPOSE(A3:W7),
    arr, TRANSPOSE(GROUPBY(EOMONTH(TAKE(tbl,, 1), 0), DROP(tbl,, 1), SUM, 3, 0)),
    VSTACK(IFERROR(TEXT(TAKE(arr, 1), "mmm-yy"), "Name"), DROP(arr, 1))
)

Resources