Is this the original VLOOKUP you are using?

=VLOOKUP(J9,'[other-worksheet.xlsx]Sheet1'!$D$3:$I$1151,1,TRUE)

Note that by using 1 as the "column index" argument you will be returning a value from the lookup range, column D. Is that the intention? TRUE as 4th argument also implies that '[other-worksheet.xlsx]Sheet1'!$D$3:$D$1151 is sorted ascending, is that the case?