The function "INDEX('C:\[Parts.xlsm]Parts'!$H$3:$H$36000,MATCH(D2,IF('C:\[Parts.xlsm]Parts'!$E$3:$E$36000=E2,'C:\[Parts.xlsm]Parts'!$D$3:$D$36000),0))" works in the actual spreadsheet. Does it require a 'then' if it is in VBA?

I tried to use 'evaluate' before it and then I got a VALUE error.

Not sure what is wrong