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

 

[Muka]

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

Yes I am using add-in

 

[Alex Blakenburg]

That together with your entire column referencing will explain why "xlookup" is so slow in your Office 2016. You are not using a native Excel function.

 

[Muka]

That together with your entire column referencing will explain why "xlookup" is so slow in your Office 2016. You are not using a native Excel function.

So ? is solution as i am using xlookup for right to left data getting. If I am using choose with vlookup then also getting slow

 

[Alex Blakenburg]

Index Match will be faster.
But I can only repeat that whole column referencing is going to kill your spreadsheet whatever method you use.

 

[Muka]

Okay I'll try

 

[Alex Blakenburg]

We can help you with Index Match if you need help.
For lookups the benefits of using Excel Tables mean.

• They dynamically expand
• You access the data by table name and column name making the formula easier to read
• Since they dynamically expand you don't have to use whole column referencing since you just select the table column and it will dynamically include additional rows.

eg. In your MTD reporting # of time 1st PC after 10 column AK using row 3
This

=XLOOKUP(AF3,'DSR 1st PC after 10'!$J:$J,'DSR 1st PC after 10'!AP:AP,"0")

becomes this (similar concept would work if you wanted to use XLOOKUP in MS 365 but since you are using O2016...)

=IFERROR(INDEX(tblDSR1st[Total],MATCH(AF3,tblDSR1st[DSR Code],0),0),0)

This <4min video will give you some ideas on Tables and Vlookups with dynamically picking the heading using match around the 2min15sec mark,
How to use VLOOKUP with an Excel Table

 

[Muka]

Thanks will try this. Is there way to use lookup right to left as if you see DSR1st PC sheet am using xlookup for that purpose since vlookup doesn't work.If i include choose then file get slow.

 

[Alex Blakenburg]

That's why I gave index match as an example, it doesn't care whether you are looking to the left or to the right. The first part (where I had Total) is the column you want to get the value from. The column in the match part is the column you want to use to figure out which row.