Forum Discussion
gjvdkamp
Jul 27, 2023Copper Contributor
Use MAP function on perpendicular ranges
If I use the formula (notice the B1:C1 horizontal and the A2:A3 vertical range, both have [1,2] values) =MAP(B1:C1,A2:A3,LAMBDA(a,b,a+b)) I would expect 2 3 3 4 But I get 2 ...
Harun24HR
Jul 28, 2023Bronze Contributor
gjvdkamp MAP() can't return result both vertically and horizontally. You actually want this formula-
=A2:A3+B1:C1
You can achieve same result by `MAKEARRAY()` lambda helper formula. Try-
=MAKEARRAY(ROWS(A2:A3),COLUMNS(B1:C1),LAMBDA(r,c,INDEX(A2:A3,r)+INDEX(B1:C1,1,c)))
- gjvdkampJul 31, 2023Copper ContributorHi thanks, I knew about the MakeArray hack, sorrt should have mentioned that. It feels a little clunky tho, wanted to verify with the product owners if the MAP could not be updated. Seems to me the logical behaviour would be to apply the function to the intersect of the ranges, like with parallel ranges.
- mtarlerJul 31, 2023Silver ContributorI get what you are saying in that Excel tends to be smart enough to know that if it gets an input that is singular in a needed dimension it will reuse that same value in that dimension. So in many cases it will apply a singular value to an entire array or matrix. In the case of the helper function MAP is explicitly MAPs element to element and if it doesn't exist it applies a #NA to the formula.
As for alternatives, yes MAKEARRAY is a work around and i'm guessing A2:A3+B1:C1 is too 'simplistic'. that said, what if you use the original LAMBDA formatting:
=LAMBDA(a,b,a+b)(A2:A3, B1:C1)
that will also work but don't know if it will work in what ever context you need.