Forum Discussion
Index - Match External document
Dear All,
I want to use the function Index & Match with 2 files. Everytime when I close my file and start the calculatin manually i get the errer message in the cell #VERW! (Dutch version).
Is there any way / function to solve this problem, like picking the data even when the other file is closed?
I'm looking forward to your reply,
Thanks in advance,
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.
4 Replies
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.
How can I merge two or more tables?
You can merge (combine) rows from one table into another simply by pasting the data in the first empty cells below the target table. The table will increase in size to include the new rows. If the rows in both tables match up, you can merge the columns of one table with another—by pasting them in the first empty cells to the right of the table. In this case also, the table will increase to accommodate the new columns.
Hope I could help you with this information.
Nikolino
I know I don't know anything (Socrates)
