Linked files - Xlookup and Filter functions

[dmacmillan]

Hello All,

Wondering if there are known performance/stability issues when using Xlookup and Filter functions with multiple linked workbooks (15). Both functions is used intra-workbook and inter-workbook. Optimisation of the Profit and Loss file requires all other files be open.

I am informed the suite of files did not have stability issues last budget cycle - these files used Vlookup extensively but did not use the Filter function. The suite of files has evolved over many years, that pre-date my involvement.

Currently fending off concerns these formulae are not fit for purpose (the inference being Vlookup is a better option). Thought I was being progressive. Granted my experience of multiple linked workbooks is lean, as I would have anticipated performance issues.

Fully appreciate the limits of Excel are being pushed. Regrettably I cannot share these files due to the number, and the commercial sensitivity.

Many thanks in advance.

Kind regards,
David

 

[jasonb75]

Are all of the workbooks open? I belive that these functions are unable to retrieve values from closed workbooks.

 

[dmacmillan]

Are all of the workbooks open? I belive that these functions are unable to retrieve values from closed workbooks.

Hello Jason,

Yes, all workbooks are open. Thank you for your counsel.

Kind regards,
David

 

[Fluff]

I think that Vlookup is faster than Xlookup, but Filter is faster than both.

 

[jasonb75]

Exactly what problems are you experiencing? Saying that you have stability issues is open to interpretation, without a more specific description of the problems you could likely end up with a lot of advice that is not relevant to the actual problem that you have.

 

[dmacmillan]

Exactly what problems are you experiencing? Saying that you have stability issues is open to interpretation, without a more specific description of the problems you could likely end up with a lot of advice that is not relevant to the actual problem that you have.

Hello Jason,

Fair point. A fellow user has encountered #REF and crashing. I myself have not encountered these issues - that said, I seldom not open all linked files.

The #REFs could be imported from linked files, I guess.

Thank you,
David

 

[dmacmillan]

I think that Vlookup is faster than Xlookup, but Filter is faster than both.

Hello Fluff,

Appreciate your counsel.

Question, can VLookups to linked files update without opening the linked files? I understand XLookup and Filter cannot function in this manner (i.e. the linked files must be open).

Thank you,
David

 

[jasonb75]

#REF means that the formula refers to a range that does not exist. If the user is opening the workbook with the formulas in first then that could be causing the problem. Filter will return #REF if the data source is not open, I'm not sure without testing but I believe that XLOOKUP will also return the same error. VLOOKUP should not return the same error unless the formula also uses INDIRECT, OFFSET, or a range union (most common causes).

 

[jaeiow]

If you can get it done without Filter, I would try that. For me it seems to add some extra cache to the file where it takes longer to mark initially. But after it is there in the cache (I have seen file size increases with Filter), it operates quickly. But with the separate workbooks, this might impinge on the cache development. I don't think a file will load into cache another file. This is definitely something to avoid overall. Microsoft recommends using one file.

 

[Fluff]

The Filter function works fine for me regardless of if the other workbook is open or not.