Thanks for the advice. I'm now struggling to understand how to define
the external range. So far I have this but the result is "#VALUE!"

Function MyLookUp(Acronym As Range, Fieldname As Range) As Variant
Dim R21Data As Range
Dim R21Fields As Range
Dim wbk As Workbook
Set wbk = Workbooks.Open(Filename:= _
"http://intranet/yesterday/Report21.xls")
Set R21Data = wbk.Worksheets("Data").Range("$A$7:$DV$1954")
Set R21Fields = wbk.Worksheets("Data").Range("$A$7:$DV$7")
MyLookUp = Application.VLookup(Acronym, R21Data,
Application.Match(Fieldname, R21Fields, 0), False)
End Function