Use MAP function on perpendicular ranges

Copper Contributor

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          #N/A

#N/A   #N/A

 

If I put them put horizontal or vertical is can map the lambda, but I need the crossproduct. I want to call a function on every combination of values (like BYROWANDCOL).

 

Is that intended behaviour or might this be addressed in a later release. ? Seems so natural to be able to apply map a lambda to perpendicular ranges? 

 

Rgds Gert-Jan

4 Replies

@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)))

 

Harun24HR_0-1690516468445.png

 

@gjvdkamp 

You have 2 arrays, each with 2 elements in your MAP.

 

MAP's return array can only be 2 elements.  You'd have to use MAKEARRAY to return the 2x2 array.

 

Additionally, you could use:

=A2:A3+B1:C1
Hi 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.
I 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.