Use MAP function on perpendicular ranges

Copper 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          #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

Re: Use MAP function on perpendicular ranges

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

Re: Use MAP function on perpendicular ranges

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.

``=A2:A3+B1:C1``