Forum Discussion
Index - Match External document
Thank you for the formula. Unfortunately references on tables don't work with closed file. The workaround could be as described here excel - Referencing a table in different workbook using IndexMatch or Vlookup only works when WB open - Stack Overflow . You may defined named range on the top of the table, i.e. if your second table with headers in A1:D20 you may define the name Range as $A$1:$D$100 to have some gap if table is expanded and work with it in formula.
INDEX/MATCH shall work with the closed file if only you don't use other functions like INDIRECT() within the formula. Perhaps you may share concrete formula?
SergeiBaklan, many thanks for your reply. The strange thing is, in the passed I did use the Vlookup and it works well but it was getting slow regarding the large amount of data.
I used the following formula "Vergelijken = Match":
=INDEX('SU VM.xlsx'!VM[[#Alles];[Status]];VERGELIJKEN([@[EAN & Fact.2]];'SU VM.xlsx'!VM[[#Alles];[EAN & Fact]];0))
After calculating the formula changes in the info below:
=INDEX('L:\TNL\ADM\Hollander Barendrecht\Plus Retail\Plus Retail 2020\Prijsverschillen 2020\Semi-Vers\SU VM.xlsx'!VM[[#Alles];[Status]];VERGELIJKEN([@[EAN & Fact.2]];'L:\TNL\ADM\Hollander Barendrecht\Plus Retail\Plus Retail 2020\Prijsverschillen 2020\Semi-Vers\SU VM.xlsx'!VM[[#Alles];[EAN & Fact]];0))
Is it maybe a simple Excel settting for allowing acces on external documents maybe?
Many thanks for your help!
Thank you for the formula. Unfortunately references on tables don't work with closed file. The workaround could be as described here excel - Referencing a table in different workbook using IndexMatch or Vlookup only works when WB open - Stack Overflow . You may defined named range on the top of the table, i.e. if your second table with headers in A1:D20 you may define the name Range as $A$1:$D$100 to have some gap if table is expanded and work with it in formula.
