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.