Forum Discussion

gjvdkamp's avatar
gjvdkamp
Copper Contributor
Jul 27, 2023

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

excel
Formulas and Functions

4 Replies

Sort By
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Jul 28, 2023

    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
  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Jul 28, 2023

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

     

     

    • gjvdkamp's avatar
      gjvdkamp
      Copper Contributor
      Jul 31, 2023
      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.
      • mtarler's avatar
        mtarler
        Silver Contributor
        Jul 31, 2023
        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.

Resources