Which is faster XLOOKUP or INDEX MATCH?

2,000,000 (yep that's million) XLOOKUP and INDEX MATCH formulas?
Which is faster XLOOKUP or INDEX MATCH?
I discovered some surprising aspects of Dynamic Array Excel during the creation of this video.
And yes...I know I should use Power Query merges or Power Pivot relationships for this size of task 😁
XLOOKUP - what is it and how did it come about?
accessanalytic.com.au/xlookup/
The easy way to do INDEX MATCH
• INDEX MATCH - The easy...
accessanalytic.com.au/blog/

[@johnhackwood1568]

Wow great video and the "@" part was a shock and just a gem of a discovery to understand , thank you. I saw this appear in older workbooks and wondered why and what it was doing.

[@AccessAnalytic]

Glad to help John, thanks for leaving the feedback

[@spilledgraphics]

07:55 " Using the @ symbol force Dynamic Arrays Excel to act like Old Excel"....wow thats a good tip!!!

[@spilledgraphics]

Hi Wyn, do you know when they are going to allow Dynamic Arrays in Excel Tables?

[@AccessAnalytic]

Cheers, it's going to be a bit of a learning curve.

[@jeschincariol]

Is there any possibility of using index match in power query?

[@AccessAnalytic]

The Power Query equivalent is the Merge button.

[@peterbartholomew7409]

There is something seriously odd about propagating the INDEX formula down the table. It wasn't even performing a search; that had already been done by the MATCH (could have been an XMATCH). Once your formula is outside the table then an INDEX should be able to return all four columns of results with = INDEX( Source, Result[Match], {2,3,4,5} ) I would put my money on that being blindingly fast [but in the light of your examples perhaps not too much money!] Sadly XLOOKUP refuses to play ball with = XLOOKUP( Result[Code], Source[Code], Source[[Colour]:[City]], "?" ) and only returns one column.

[@usmaniqbal1836]

Please share link to download file for practice

[@AccessAnalytic]

I often didn’t keep these files in the past but I’m trying to share links in newer videos.

[@usmaniqbal1836]

Please share excel file..

[@AccessAnalytic]

Unfortunately I didn’t keep the files 2 years ago. I’m now adding links for my new video