Index Match Function Within One Workbook Referencing Cells in Another workbook error

Hi everyone!

I have a indexmatch function running in one of my workbooks that references cells in another workbook. The function works perfectly when the other workbook is open. However, once the workbook is closed it displays a #REF! error. I have automatic update of links turned on but I keep getting this error. Any help would be very much appreciated. Thanks

Hi XOR LX!

Here is the formula I've used

=IF($E$6="","",INDEX('HYIA fleet avionics.xlsx'!Table1[AUDIO],MATCH($E$6,'HYIA fleet avionics.xlsx'!Table1[Aircraft],0)))

Once the workbook is closed (HYIA fleet avionics the links automatically update to

=IF($E$6="","",INDEX('C:\Documents and Settings\mx\Desktop\MRO Projects\HYIA fleet avionics.xlsx'!Table1[AUDIO],MATCH($E$6,'C:\Documents and Settings\mx\Desktop\MRO Projects\HYIA fleet avionics.xlsx'!Table1[Aircraft],0)))

but I still get the #REF! error in the cell of the other workbook. As long as the workbook is open, it works but I'd like it to work otherwise as well. Thanks!

Hi XOR LX!

I managed to figure it out. The issue was with the table referencing! When referencing closed workbooks, specific ranges have to be mentioned in the formula for it to work. The table referencing returns the #REF! error when the workbook is closed. I've switched to using Vlookup instead as the only reason I used Index Match was to allow me to insert columns and rows within the table and automatically still maintain accurate results with the referencing. Since I can't do that, I switched to using Vlookup instead. It still doesn't give me that functionality but atleast the formula is slightly shorter!

Regards