Forum Discussion
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...
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!")) )@
This is fascinating because colA and colB are evaluating when I step through the MAP - Excel just won't calculate it. The simplest explanation that comes to mind is Excel is reading colA/colB as full on arrays in the context of the LAMBDA.
Naturally, it works when I write it as a legacy CSE MAX-IF
=LET(item, "A", colA, A2:A7, colB, B2:B7, ux, UNIQUE(colA),MAP(ux, LAMBDA(item, MAX(IF(colA=item,colB)))))