Variable index lookup based on cell value

 Thanks for response Guy.

I do need it work without opening the workbook. I have found a work round using the formula below a bit long winded but its functional my only problem with this is I want a blank value if no result is found and the formula is too long for an iferror command or at least thats what excel tells me.

=IF(Estimate!C4="1",INDEX('[170 Ops Centre 2018.xlsm]FPS_Parts'!$F$3:$F$9000,MATCH(Estimate!B49,'[170 Ops Centre 2018.xlsm]FPS_Parts'!$B$3:$B$9000,0)),IF(Estimate!C4="2",INDEX('[170 Ops Centre 2018.xlsm]FPS_Parts'!$F$3:$F$9000,MATCH(Estimate!B49,'[170 Ops Centre 2018.xlsm]FPS_Parts'!$B$3:$B$9000,0)),IF(Estimate!C4="3",INDEX('[170 Ops Centre 2018.xlsm]FPS_Parts'!$F$3:$F$9000,MATCH(Estimate!B49,'[170 Ops Centre 2018.xlsm]FPS_Parts'!$B$3:$B$9000,0)),IF(Estimate!C4="4",INDEX('[170 Ops Centre 2018.xlsm]FPS_Parts'!$F$3:$F$9000,MATCH(Estimate!B49,'[170 Ops Centre 2018.xlsm]FPS_Parts'!$B$3:$B$9000,0)),IF(Estimate!C4="5",INDEX('[170 Ops Centre 2018.xlsm]FPS_Parts'!$F$3:$F$9000,MATCH(Estimate!B49,'[170 Ops Centre 2018.xlsm]FPS_Parts'!$B$3:$B$9000,0)),IF(Estimate!C4="6",INDEX('[Bibbys Ops Center.xlsm]FPS_Parts'!$F$3:$F$90000,MATCH(Estimate!B49,'[Bibbys Ops Center.xlsm]FPS_Parts'!$B$3:$B$90000,0)),IF(Estimate!C4="7",INDEX('[170 Ops Centre 2018.xlsm]FPS_Parts'!$F$3:$F$9000,MATCH(Estimate!B49,'[170 Ops Centre 2018.xlsm]FPS_Parts'!$B$3:$B$9000,0)),"")))))))

 Currently I only get a blank value if the Value contained within Estimate!C4 is not parts of the formula but if Estimate!C4 value is either 1,2,3,4,5,6,7 and no result is found then it returns a #N/A.otherwise it works

Is there a way to do this so that I get a blank value if no result is found? Should I be looking at a Macro instead? and if so can this lookup a closed workbook in the same way as this formula?