Xlookup issue

[Muka]

Hi,

After using xlookupFile detail formula my file goes in calculating mode each time when i do copy/Past row or do make any change is base data. My file is getting super slow.

File dump few sample data https://we.tl/t-5oo3DkSr0k

 

[Alex Blakenburg]

You are using a lot of lookups and they are all using "full column" referencing. So you are making each lookup look at over 1M rows.
You might want to consider using Tables and use Table structured referencing so it only looks at the 2k rows that are actually being used.

 

[Muka]

You are using a lot of lookups and they are all using "full column" referencing. So you are making each lookup look at over 1M rows.
You might want to consider using Tables and use Table structured referencing so it only looks at the 2k rows that are actually being used.R

 

[Muka]

Yes as there is multiple source of data thats why multiple lookup is there. Same when i was using Vlookup then it was not facing such issue.

 

[Alex Blakenburg]

When I downloaded your workbook all your XLookups had a prefix of "@" is that the way it transferred via the sharing platform or do you have the "@" at your end as well ?
Your profile says you have both MS 365 and Office 2016. Have you by any chance opened the file in 2016 and then resaved it ?

 

[Muka]

When I downloaded your workbook all your XLookups had a prefix of "@" is that the way it transferred via the sharing platform or do you have the "@" at your end as well ?
Your profile says you have both MS 365 and Office 2016. Have you by any chance opened the file in 2016 and then resaved it ?

Dear, I have 0ffice 2016 and in my excel file i do not have @ in formula.

 

[Alex Blakenburg]

I don't why XLookup would perform worse than XLookup unless it is because Office 2016 is quite an old version to have XLookup and has had XLookup retrofitted to it, it may not be optimised to fully utilise it. I would still suggest that that many lookup in a workbook all using entire column referencing is bound to cause the spreadsheet to get really slow and that you might want to look into using tables or at least limit the number of rows being used in the Lookups.

 

[Fluff]

I have 0ffice 2016

2016 does not have Xlookup, so are you using an add-in that replicates the Xlookup function?

 

[Alex Blakenburg]

@Fluff, I didn't think it was available in 2016 when making my post #5, then the MS document page confused me, referencing 2016 & 2019 at the top of the page. Only after revisting it post your comment did I notice that slightly further down the page there is a note to say it does not exist in 2016 & 2019.

@Muka, please let us know if you are running the XLookup AddIn.

XLOOKUP function

 

[Fluff]

Those Support pages can often be confusing & sometimes totally wrong, which is not very helpful.