XLOOKUP stops matching after 18k rows

Hello everyone,

I can't figure out why XLOOKUP can't find any mappings after a certain number of rows and instead only returns the [IF_NOT_FOUND] value.

I have a source sheet "W2" which contains timestamp/datetime and value data. Because of some time gaps within data, I created a "Master" sheet with a complete time series (no data gaps) and tried to map via XLOOKUP the values of W2 to the correct datetime in the Master sheet. Goal is to get a complete series with only gaps in value column.

With VLOOKUP I have the same phenomenon, only that it occurs with a different number of rows.

My original file is way longer (until end of 2022), I have cut it to June 2018 for the sake of reducing file size (upload limit 1 MB here).

I have MS365 Subscription on Win11, all latest version: Microsoft® Excel® for Microsoft 365 MSO (Version 2404 Build 16.0.17531.20152) 64-bit

I dont even know where to start troubleshooting. I have tried to alter the formular and used decimal date time but none worked. I dont even know if this is due to my local PC (lenovo P14s) or other non software related reason.

Help much welcome

Try this:

=XLOOKUP(A2,'W2'!$A$2:$A$23504,'W2'!$B$2:$B$23504,"",1,1)