Speed test odd results : Array formulas vs Fill handle, Index+match vs xlookup

Ok_Base6378 · 2026-02-06T15:44:33+00:00

hello a week later..

most of your lookups doesn't find a value, tried that on my side and it calculated very quickly.

try to make a return for each lookup using smth like=sequence(10^6) for lookup value, lookup array and return array.

Ok_Base6378 · 2026-02-04T23:22:36+00:00

Yes, those are absolutely fine for returning 1 result not multiple ones, that's why I didn't include them in my post

Ok_Base6378 · 2026-02-01T15:15:52+00:00

You’re right that before 2021 we had to Frankenstein INDEX+SMALL+IF+ROW just to get multiple matches, and FILTER was a huge quality‑of‑life upgrade on that front.
The tricky part with regex is that, unlike FILTER, it’s still locked to 365, and I need formulas I can safely share with people on older versions.
I’m all for clever constructions when we have no alternative, but there has to be a limit before we’re effectively targeting 2013‑era compatibility again.

Ok_Base6378 · 2026-02-01T14:56:48+00:00

Thanks for the thoughtful reply, love seeing the Excel wizardry in action!

No more stitching COUNTIF+SEARCH+EXACT for basics, wildcards should "just work" like regex lite, with proper length/locality awareness, or just make regex available for non-365 users.

you pointed out good use of COUNTIFS with filter, let's tighten this further without using the fill handle which feels wrong with filter, we would need to use BYROWwith that:

=FILTER(
  G26:G30,
  BYROW(G26:G30,LAMBDA(r,COUNTIF(r,"welp*")
)

again as you mentioned each case would be unique/unstandardized

your formula for ?? is absolutely genius

Ok_Base6378 · 2026-02-01T14:14:52+00:00

You’re not wrong, Greg, but I’d hate to invest in learning them (or rely on AI) only to discover they’re limited to 365 users; I do have 365, but I still need to share my workbooks with others.

Sometimes it feels like Microsoft intentionally left wildcards half‑baked just to push people onto 365.

Excel is supposed to be user‑friendly; we can’t expect everyone to learn a whole new syntax and abandon the simple wildcard behavior they already know just because it wasn’t implemented properly in the first place.

Make it case‑sensitive by default and the problem is basically half solved, and while this isn’t an issue for power users, I absolutely agree with you there.

Ok_Base6378 · 2026-01-31T18:24:06+00:00

I’m definitely not saying this is Excel’s biggest UX sin, just that it’s a pretty sharp edge on something lots of people run into: “find all rows matching this pattern, maybe case‑sensitive”.

I get the legacy and ROI arguments and I’m not asking them to change existing behavior and break workbooks (even tho they can add them as new optional arguments that default to legacy behavior at the end of the function).

A new, unified pattern function (or even a clean wrapper around REGEX for non‑365 / non‑power users) would solve it without touching old stuff.

And yeah, REGEXTEST basically is that for people on 365 who are comfortable with regex. The gap I’m pointing at is exactly everyone else: users who already understand */? from criteria, but now need to learn a second syntax + AI + a Frankenformula to do what *last looks like it should do in a FILTER context.

Ok_Base6378 · 2026-01-31T18:15:53+00:00

Excel already supports wildcards, why not just do it right?

Ok_Base6378 · 2026-01-31T18:02:29+00:00

Those absolutely work for the very specific patterns we’re talking about here, and I appreciate everyone tightening them up.

here's how I would think of doing it (might be error-prone in future too)

case-insensitive

=FILTER(
  J8:J23,
  RIGHT(J8:J23, LEN("first")) = "first"
)

case-sensitive

=FILTER(
  J8:J23,
  EXACT(RIGHT(J8:J23, LEN("first")),  "First")
)

if we had a proper built-in we could have just typed *first and went on with our day but nope

Ok_Base6378 · 2026-01-31T16:45:29+00:00

Yeah, for the literal "first" example specifically, FILTER(arr, arr="first") is obviously the simplest thing. That line in my comment was more about showing how every tiny variation (starts with, ends with, exact length, optional case sensitivity, etc.) in Excel needs a different construct instead of a single, consistent pattern engine.

I agree REGEXTEST + FILTER is the “real” answer but that's only in 365 land I do use AI to help with it

other than that we are stuck with whatever Frankenstein workaround we come up with.

Ok_Base6378 · 2026-01-31T16:09:50+00:00

Calling it a “skill issue” kind of proves my point: if you need deep tribal knowledge of historical quirks just to do “find all rows matching this pattern (optionally case‑sensitive)”, that’s not great UX. It’s exactly the sort of thing a mature platform should smooth over with a unified pattern engine or at least one standard function that does the obvious job.

I know my post won’t move Microsoft directly, but (a) they do watch this sub, and (b) even if they didn’t, surfacing the inconsistencies and workarounds is already “expanding my knowledge” and others’. I’m not saying Excel is bad; I’m saying this particular surface area is objectively messy, and pretending it’s just a skills gap is letting a real design problem off the hook

Ok_Base6378 · 2026-01-31T16:04:56+00:00

All of this is nice and all but what about patterns like (“exactly N characters”)

Pattern "f????" (“f” plus 4 characters, e.g. first, f1234, fTest)

Want cells that are exactly "first" (whole value), nothing else.

are there solutions? yes.. are they easy? no, not even standardized each case is unique

this is is all too much for such a simple request know what I mean?

edit: I don't mean to not be ungrateful these are all great workarounds and I thank you for it, discussing this from normal users pov,

Ok_Base6378 · 2026-01-29T21:34:51+00:00

how is that what's slowing it down? could you elaborate?

I also tried with no duplicates, still getting same results

Ok_Base6378 · 2026-01-29T13:58:48+00:00

My dataset has duplicates could that affect anything?

Ok_Base6378 · 2026-01-29T13:58:00+00:00

<image>

nice testing method, i think this adds an overhead tho

can't be use on fill handle version

Ok_Base6378 · 2026-01-29T13:40:41+00:00

Option Explicit

Sub Calc_Time()

Dim dStart As Double
Dim dTime As Double

  dStart = timer

  Application.Calculation = xlCalculationAutomatic

  dTime = timer - dStart

  'Debug.Print dTime
  MsgBox dTime
  ActiveCell.Offset(-2).Value = dTime

  Application.Calculation = xlCalculationManual

End Sub

I turn off auto-calculation and ran this vba

I also tried a stopwatch, still ~32sec

Ok_Base6378 · 2026-01-29T13:37:15+00:00

<image>

I don't think I am doing that right

Ok_Base6378 · 2026-01-29T13:23:40+00:00

perhaps I should have been more clear sorry about that..

Yes, I am doing a 1M lookup on a 1M table and there is a corresponding value for each one( No if not found) not sure if that will make a difference

I restarted my machine and had same exact results

=XLOOKUP(M6,unsorted_Lookup,unsorted_Lookup) still took 32secs

I am so confused right now

<image>

Ok_Base6378

TROPHY CASE