What is fastest formula and input setting between INDEX-MATCH, INDEX-XMATCH, and XLOOKUP?

[gifariz]

Has anyone tried more detail about speed test for value lookup formulas?
I tried myself but I doubt my experiment. Please see my screenshot.

The lookup formulas to compare: (1) INDEX-MATCH, (2) INDEX-XMATCH, (3) XLOOKUP.
Formula setting to compare: (1) whole column vs fixed range reference, (2) exact match vs binary search (ascending/descending), (3) single vs array input

We mainly work with large data (>100,000 rows), so we want to know the fastest lookup formula, but still using easiest input setting.
For us, preferrable setting are:
(1) whole column reference, because reference table size is changing and making dynamic reference is mouthful (e.g. OFFSET(B3,0,0,COUNTA(B:B)-2) instead of just B:B);
(2) exact match, because we dont have to sort the reference first;
(3) single input (and then auto-fill), because again input table size is changing and making dynamic reference is mouthful.
The fastest setting would probably be the opposite of all of these three preferrable settings,
but we are doing many unique kinds of analyses, we need to make new calculation/formula many times, so mouthful formula isn't preferrable to write and read/edit.
There are also other optimization techniques like xmatch only in one column then use offset (instead of index) in other columns, but I'd like to focus on direct use of these three formulas.

From my experiment (see my screenshot), it is found that INDEX-XMATCH (no. 3) is the fastest among preferred input setting.
But we actually want to use XLOOKUP (no. 5) because it is shortest to write, but it is awfully slow for non-array input.
So I wonder whether my experiment is correct? Like I worry if there is overhead to populate >100,000 rows during auto-fill but I dont think it is the case.
Btw I record time just by hand stopwatch.

Looking forward to your thoughts, thank you.

 

[arthurbr]

To start with, I would avoid using entire columns to cope with dynamic ranges, but make the range an Excel Table.
Avoid using OFFSET as it is a volatile function and will slow things down.
As you only provided an image, I can't work on it, but the fastest solution IMO is using Power Query. One of many examples can be found at How to Perform VLOOKUP in Power Query in Excel - A Step by Step Guide

 

[Alex Blakenburg]

I agree with arthur on thinking Power Query is the way to go on larger datasets. There are a number of speed tests out there with different results. Here is one by Chandoo, video is 14 mins but results are shown at the 10.5 minute mark, with XLookup winning using 11m lookups. It also results in the smallest excel file size.

 

[gifariz]

Thank you arthur and alex. Both your answers are equally helpful, unfortunately I can mark only one solution.

To start with, I would avoid using entire columns to cope with dynamic ranges, but make the range an Excel Table.
Avoid using OFFSET as it is a volatile function and will slow things down.
As you only provided an image, I can't work on it, but the fastest solution IMO is using Power Query. One of many examples can be found at How to Perform VLOOKUP in Power Query in Excel - A Step by Step Guide

Sorry, I missed to upload the excel file.
I have tried power query but it seems too difficult for us, because we are calculating many things and need many formulas (not only lookups) that are quite different in power query.
I will explore Excel Table more and see if it is practical and not too difficult for us. Thank you.

I agree with arthur on thinking Power Query is the way to go on larger datasets. There are a number of speed tests out there with different results. Here is one by Chandoo, video is 14 mins but results are shown at the 10.5 minute mark, with XLookup winning using 11m lookups. It also results in the smallest excel file size.

Thank you, I forgot to look at youtube. That particular video seems to not compare INDEX-XMATCH which is a bit faster in my experiment, but yeah XLOOKUP is better in general (convenient, smaller file size, fast enough) when lookup range is not whole column.