Forum Discussion
XLOOKUP link to another workbook doesn't work if workbook closed
Hello, I have an XLOOKUP in one worksheet that is pulling data from a reference table in a separate workbook. The lookup works fine if the reference workbook is open, but if it is closed, I get a #...
Don't know if this issue was solved - but I think I know what's the problem here. Xlookup wants the same number of rows for each lookup so if you used name manager or referenced to a power query table it might not show correctly. For example =XLOOKUP($A$3:$A$200&$B$3:$B$200&$C$3:$C$200&$F$3:$F$200;'MV Makelaars Navrae.xlsm'!Navrae_2[KLIENT]&'MV Makelaars Navrae.xlsm'!Navrae_2[POLIS '#]&'MV Makelaars Navrae.xlsm'!Navrae_2[VERSEKERAAR]&'MV Makelaars Navrae.xlsm'!Navrae_2[TIPE POLIS];'MV Makelaars Navrae.xlsm'!Navrae_2[PIETER TERUGVOERING];"";0) the formula is correct but it will give an error as an answer change it to as example
=XLOOKUP($A$3:$A$200&$B$3:$B$200&$C$3:$C$200&$F$3:$F$200;'[MV Makelaars Navrae.xlsm]Navrae'!$A$3:$A$200&'[MV Makelaars Navrae.xlsm]Navrae'!$B$3:$B$200&'[MV Makelaars Navrae.xlsm]Navrae'!$C$3:$C$200&'[MV Makelaars Navrae.xlsm]Navrae'!$F$3:$F$200;'[MV Makelaars Navrae.xlsm]Navrae'!$H$3:$H$200;"";0) This might solve your issue
=XLOOKUP($A$3:$A$200&$B$3:$B$200&$C$3:$C$200&$F$3:$F$200;'[MV Makelaars Navrae.xlsm]Navrae'!$A$3:$A$200&'[MV Makelaars Navrae.xlsm]Navrae'!$B$3:$B$200&'[MV Makelaars Navrae.xlsm]Navrae'!$C$3:$C$200&'[MV Makelaars Navrae.xlsm]Navrae'!$F$3:$F$200;'[MV Makelaars Navrae.xlsm]Navrae'!$H$3:$H$200;"";0) This might solve your issue
This is a known limitation:
Excel has limited support for dynamic arrays between workbooks, and this scenario is only supported when both workbooks are open. If you close the source workbook, any linked dynamic array formulas will return a #REF! Error when they are refreshed.
Dynamic array formulas and spilled array behavior (microsoft.com)
- Mine does not show #REF and I don't have both workbooks open. I used Xlookup as per my reply in the previous message. And I work with more than one workbook doing the same thing...works 100% for me with no #REF totals even on refresh. Maybe it's luck. 🙂 Maybe you should try it yourself to see its works with no #REF error while one workbook is closed. Well, I found a way and working with this workbook since xlookup rolled out think it was 2020. if your xlookup are 20 rows make sure the 20 rows array are followed through all the way. If one array is longer make all the same. Hope it make sense. 🙂 Like I said it works for me - Tried and tested.
- THIS DOES NOT WORK. Microsoft even states it as a limitation.
Xlookup does not find data from other spreadsheets if the spreadsheet is not open. I have the same issue.
LizeMarie Try saving over those spreadsheets you are pulling from (looking up from) and open your sheet that references them WITHOUT ever opening your reference sheets and I don't think you will get updates. I suspect you have a macro running in the background helping you out or cache data. If you wouldn't mind, can you reply to me the process you are taking to get your import to work without opening the reference files.
