Forum Discussion
MAXIFS doesn't work inside MAP using names from LET
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!")) )@
davidleal This is interesting. It appears it is taking the named ranges and trying to do some array function on then giving the array of array error. I tried a few variation and found using the 'reduce' workaround produced an array of 18 (i.e. 3*6) and supports the array of array issue. Still not sure why and even tried adding $$ to the ranges and creating new names as functions of the original with no difference. By the way, the FILTER work around can be simply:
MAP(ux, LAMBDA(item, MAX(FILTER(colB, colA=item,0))))I attached my sheet if you are curious of the different things I tried.