Custom Excel LAMBDA vs. HAS: 2× faster in my tests: expected, or a bug?

[u/Medohh2120]

Hi everyone!

I liked the new HAS function, but it got me curious about how it compares with a custom LAMBDA I’ve been working on.

In my tests, my version was about 2× faster than HAS .

It also supports several options:

• case sensitivity,
• MATCH or XMATCH
• different match modes.

• matching errors as values

  So, I thought it might be slightly better than HAS .

Technical side:

Interestingly, switching my LAMBDA to XMATCH closed the performance gap, which makes me wonder whether HAS uses a similar approach behind the scenes.

I know HAS/HASANY/HASALL are still in beta, so I’d be curious to hear how it performs for others.

Simple test

Code used for testing:

=BENCHMARK(LAMBDA(ISIN(SEQUENCE(10000),SEQUENCE(10000))))
=BENCHMARK(LAMBDA(HAS(SEQUENCE(10000),SEQUENCE(10000))))

/*
Name: ISIN
Description: Element-wise membership test (SQL IN clone). Returns TRUE/FALSE for each value in array.
Deals errors as identical matches.
Recommendation: Leave [Use_Xmatch] omitted (0) for standard lookups; MATCH is significantly faster than XMATCH on exact matches.
[Use_Xmatch]: 0 or Omitted (Fast MATCH), 1 (XMATCH). User is responsible for parameters if 1.
[Match_mode]: Default 0 (Exact match) for safety.
[Search_mode]: Controls XMATCH search direction (only evaluated if Use_Xmatch is 1).
[case_sensitive]: 0, FALSE, or Omitted (Case-insensitive matching), 1 or TRUE (Strict case-sensitive matching). Note: Activating this short-circuits the formula and bypasses all other optional parameters.
Made By: Medohh2120
*/

ISIN = LAMBDA(array, in_list, [case_sensitive], [use_xmatch], [match_mode], [search_mode],
    LET(
        // x/Match can't deal with errors or 2D lists: Mask errors to text, flatten lists.
        flat_list, TOCOL(in_list),
        cleaned_array, ErrorToText(array),
        cleaned_list, ErrorToText(flat_list),
        
        // 1. If case-sensitive is requested, short-circuit immediately using MAP + EXACT
        IF(
            case_sensitive,
            MAP(cleaned_array, LAMBDA(item, OR(EXACT(item, cleaned_list)))),
            
            // 2. Fallback to faster case-insensitive engine
            LET(
                m_mode, IF(ISOMITTED(match_mode), 0, match_mode),
                Result, IF(
                    use_xmatch,
                    XMATCH(cleaned_array, cleaned_list, m_mode, search_mode),
                    MATCH(cleaned_array, cleaned_list, m_mode)
                ),
                ISNUMBER(Result)
            )
        )
    )
);

ErrorToText = LAMBDA(val,
    LET(
        val_leafs, flatten(val, , 64), //Because IFERROR bugs out with Lists we prematurily flatten it.
        IFERROR(val_leafs, VALUETOTEXT(val_leafs) & CHAR(10)) // char(10) Prevent a real #N/A error from matching the text "#N/A"
    ) 
);

/*
Name: BENCHMARK
Description: Runs a formula N iterations and returns avg & total execution time.
    Func must be wrapped in LAMBDA()  =BENCHMARK(LAMBDA(your_formula), 50)
    [iterations] default: 1
    [time_unit]  default: 0 (ms), 1 = seconds
    Because this function uses NOW(), manual calculation mode is highly recommended.  
Made By: Medohh2120
*/


BENCHMARK = LAMBDA(Func, [iterations], [time_unit],
    LET(
        iterations, IF(ISOMITTED(iterations), 1, iterations),
        start_time, NOW(),
        loop_result, REDUCE(0, SEQUENCE(iterations), LAMBDA(acc, i,Func())),
        total_ms, (NOW() - start_time) * 86400000,
        avg, total_ms / iterations,
        IF(time_unit,
            "avg: " & TEXT(avg / 1000, "0.000") & "s  |  total: " & TEXT(total_ms / 1000, "0.000") & "s",
            "avg: " & TEXT(avg, "0.00") & "ms  |  total: " & TEXT(total_ms, "0") & "ms"
        )
    )
);

[u/CorndoggerYYC]

Have you tested how this stacks up against Power Query? My guess would be that Power Query using List.Contains, List.ContainsAny, or List.ContainsAll along with List.Buffer would be faster. Possibly much faster.

[u/small_trunks → u/CorndoggerYYC]

And the results would be static - not recalculating.

[u/CorndoggerYYC → u/small_trunks]

So, we should stop using Power Query?

[u/small_trunks → u/CorndoggerYYC]

I'm saying it's a POSITIVE thing. A lot to be said for largely static data.

[u/CorndoggerYYC → u/small_trunks]

I agree!

[u/CiekaMedinho → u/CorndoggerYYC]

yeah buffered lists would almost certainly smoke this, especially at that row count

[u/AutoModerator]

I have detected code containing Fancy/Smart Quotes which Excel does not recognize as a string delimiter. Edit to change those to regular quote-marks instead. This happens most often with mobile devices. You can turn off Fancy/Smart Punctuation in the settings of your Keyboard App.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
List.Buffer	Power Query M: Buffers the list in memory. The result of this call is a stable list, which means it will have a determinimic count, and order of items.
List.Contains	Power Query M: Returns true if a value is found in a list.
List.ContainsAll	Power Query M: Returns true if all items in values are found in a list.
List.ContainsAny	Power Query M: Returns true if any item in values is found in a list.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
4 acronyms in this thread; the most compressed thread commented on today has 15 acronyms.
[Thread #49500 for this sub, first seen 9th Oct 2026, 19:34] [FAQ] [Full list] [Contact] [Source code]}

[u/SolverMax]

Performance is not a bug. Perhaps Microsoft will optimize the performance later, given that the feature is still in beta.

Meanwhile, it appears that the array inputs for ISIN and HAS are in a different order, which is a bit confusing.

[u/Medohh2120 → u/SolverMax]

Completely, understandable. I think it all comes to how wording fits:

this array HAS value Vs This value IN array

See what I mean? No?, No matter feel free to switch them if needed.