#1
|
|||
|
|||
Index/match accross two workbooks
I am having difficulty trying to get index/match to work across two workbooks. I've looked at several forums and this is a common topic. I've not seen a solution, only statements like "There is no reason this won't work". It doesn't seem to work in Excel 2010 (unless I just don't know how to use it). I don't know if it works in other versions of Excel.
Worksheet "DesignValues" in Workbook A contains a table of design values. Workbook B is a project that needs to pull values from "DesignValues". My formula is: =INDEX(Fb,MATCH(1,INDEX((A4=SYSTEM)*(B4=SPECIE)*(C 4=SIZE)*(D4=GRADE),0),0))) The formula works if it is in the same worksheet as the data ranges SYSTEM, SPECIE, SIZE, GRADE & Fb. However, when I try to use it in another workbook I get "N/A". When the formula is located in the same worksheet the range names are in the form of =DesignValues!$A8$A146 Scope = Workbook When the formula is located in another workbook the range names are in the form of ='[WORKBOOK A.xls]DesignValues'!$A$8:$A$146 Scope = Workbook I've tried IFERROR & ISERROR and I get a nice 0. I've created a more simpler index/match formula and the problem seems to be that match/index just doesn't work across workbooks. Any insight to this problem would be appreciated. |
#2
|
||||
|
||||
Assuming you've done the referencing correctly, the formula will work BUT you will need the source workbook to be open for the formula to return a result. That's quite possibly why you're getting the "N/A" errors. You can get around that by creating a reference sheet in your target workbook that explicitly references each cell in the source workbook, then point the formula in your target workbook to that new sheet (which can be hidden and protected, if needs be).
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Translate INDEX(MATCH) into VBA | grexcelman | Excel Programming | 1 | 03-08-2015 05:06 PM |
Index match vba | grexcelman | Excel | 0 | 03-05-2015 10:55 PM |
Index Match syntax | grexcelman | Excel Programming | 0 | 03-05-2015 07:17 AM |
UDF multiple IFs INDEX-MATCH | grexcelman | Excel Programming | 0 | 02-22-2015 04:20 PM |
Index Match function | jackzha | Excel | 5 | 12-03-2014 12:43 PM |