+ Reply to Thread
Results 1 to 2 of 2

Index Match to data in other workbook

Hybrid View

  1. #1
    Registered User
    Join Date
    08-14-2017
    Location
    Belgium
    MS-Off Ver
    365
    Posts
    48

    Index Match to data in other workbook

    Good Day!

    I searched the internet but can't find the right way to get it to work. Don't know where to put the : [] \ .....
    I want an index match which retrieves data that is located in another workbook (which is not open/active, no idea if this important).

    =INDEX(X:\Data\Metro Communication\01 - AUTOMATIC ORDERING METRO\03 - AANVRAAG - DEMANDE\STOCKMODEL voor templates.xlsb:[21]$H$1:$Y$70000;MATCH(B8;X:\Data\Metro Communication\01 - AUTOMATIC ORDERING METRO\03 - AANVRAAG - DEMANDE:[STOCKMODEL voor templates.xlsb]21'!$H$1:$H$70000);MATCH($C$7;X:\Data\Metro Communication\01 - AUTOMATIC ORDERING METRO\03 - AANVRAAG - DEMANDE:[STOCKMODEL voor templates.xlsb]21'!$H$1:$Y$1))
    Path = X:\Data\Metro Communication\01 - AUTOMATIC ORDERING METRO\03 - AANVRAAG - DEMANDE
    File = STOCKMODEL voor templates.xlsb
    Sheet= 21

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    6,330

    Re: Index Match to data in other workbook

    The easiest way to do it is to open both workbooks, and create the formula by selecting the ranges as needed, and making sure that the formula works. Then close the workbook that you want to create links to, and the references will properly update.

    For example:

    =INDEX('[Workbook.xlsm]Sheet'!$A:$A,MATCH(A2,'[Workbook.xlsm]Sheet'!$AC:$AC,FALSE))

    Becomes

    =INDEX('\\Path\[Workbook.xlsm]Sheet'!$A:$A,MATCH(A2,'\\Path\[Workbook.xlsm]Sheet'!$AC:$AC,FALSE))

    That way, you don't have to worry about the proper placement of brackets and quotes....

    Just be aware that the formula probably won't work well with the other book closed because it isn't a simple link and basically requires that the other workbook be opened by Excel in the background and then closed. So it may just be better to open both workbooks from a performance perspective.
    Last edited by Bernie Deitrick; 04-14-2021 at 05:30 PM.
    Bernie Deitrick
    Excel MVP 2000-2010

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. INDEX MATCH across whole workbook
    By DG Silva in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-10-2021, 05:33 PM
  2. [SOLVED] Index Match to closed Workbook
    By Gazsim in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-01-2019, 12:31 PM
  3. Index Match Different Workbook with Different Worksheet
    By bewarehee in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-06-2017, 01:54 PM
  4. [SOLVED] INDEX/MATCH from Separate Workbook(s)
    By krista_o in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-19-2014, 05:08 PM
  5. [SOLVED] Index Match Function Within One Workbook Referencing Cells in Another workbook error
    By Hackboss007 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-18-2014, 12:06 PM
  6. Index/Match lookup in another workbook.
    By Mike_Taylor16 in forum Excel General
    Replies: 10
    Last Post: 06-29-2012, 10:08 AM
  7. INDEX & MATCH data from other workbook
    By faffol in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-30-2012, 06:16 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1