Forum Discussion

davidleal's avatar
davidleal
Iron Contributor
Nov 20, 2022
Solved

MAXIFS doesn't work inside MAP using names from LET

Just for testing purpose, I have the following formula showing different scenarios at the same time via HSTACK:         =LET(item, "A", colA, A2:A7, colB, B2:B7, ux, UNIQUE(colA), noMapOut, M...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Nov 21, 2022

davidleal 

Explanation (dereferencing ranges) and some workaround (thunking) are given here https://www.linkedin.com/feed/update/urn:li:groupPost:12656547-6951207529683001345/?commentUrn=urn%3Ali%3Acomment%3A%28groupPost%3A12656547-6951207529683001345%2C6951232517186203648%29 by lori_m 

Using his suggestion that could be like

=LET(
    item, "A",
    colA, LAMBDA(A2:A7),
    colB, LAMBDA(B2:B7),
    ux, UNIQUE(colA()),
    noMapOut, MAXIFS(colB(), colA(), item),
    mapOut1, MAP(ux, LAMBDA(item, MAXIFS(B2:B7, A2:A7, item))),
    mapOut2, MAP(ux, LAMBDA(item, MAXIFS(colB(), colA(), item))),
    HSTACK(noMapOut, mapOut1, IFERROR(mapOut2, "#CALC!"))
)

@ 

Resources