2

Ever since 2021 I've had acces to the newer lambda's helper functions among which was the MAP() function. All other helpers like BYROW(), BYCOL(), SCAN() and REDUCE() amongst others are working fine. Though the firstmentioned MAP() function is recognized by the Excel's auto-complete feature it will error out when applied. Let me show you in a few screenshots:

Auto-complete picking up on MAP():

enter image description here

Error "Invalid function" upon completion:

enter image description here

The strange part is that I can actually apply the formula through VBA:

Range("C1").Formula2 = "=MAP(A1:A2,LAMBDA(a,IF(a=1,a,a*2)))"
Resulting in:

enter image description here

Upon altering the formula I recieve the same error:

enter image description here

Up to now I have left this be, but I recently had a query where I'd have like to have used MAP(). Anyone seen this before/knows what the fix is?

Excel version: ms365 version 2211 build 15813.20002

UPDATE 1: Starting to get the feeling it has to do with local Dutch version of Excel since the following would also produce an error 1004 through VBA:

Range("C1").Formula2Local = "=MAP(A1:A2;LAMBDA(a;ALS(a=1;a;a*2)))"

UPDATE 2: Switched my Office display settings to English (United States) through File > Options > Language, let Excel update, and tried MAP(). It worked! Changing back to Dutch broke the function again though.

I guess the problem is a bug and I'll report it as such.

Improve this question
5
  • 1
    Great thanks for solving your own problem and providing feedback! I noticed you used semi-colons when entering the formula via the cell and comma's via VBA. So is it a language or delimiter setting issue?
    – ExcelEverything
    Commented Oct 21, 2022 at 7:28
  • Also, greetings from .ZA! Clearly you are dutch, why the South african flag?
    – ExcelEverything
    Commented Oct 21, 2022 at 7:29
  • Hey @ExcelEverything, yeah it appears to be something with the delimiter. And it's only with this particular function. Ticket has been sent. Also, yes I'm Dutch but my wife and extended family are South African.
    – JvdV
    Commented Oct 21, 2022 at 7:35
  • Ah thought something like that. Do you have an excel solution to solve the issue of loadshedding? :.)
    – ExcelEverything
    Commented Oct 21, 2022 at 7:52
  • Haha, wife's parents live on the same grid as the nearby hospital. Near to no loadshedding. Wouldn't rely on Office to solve incompetence with DOE and Eskom.
    – JvdV
    Commented Oct 21, 2022 at 7:55

0

Reset to default

You must log in to answer this question.

Browse other questions tagged .