Created attachment 42404 [details]
Zip file containing spreadsheet samples and screenshot image
This problem occurs on both my Ubuntu Linux (64 bit) and in Windows XP SP3.
When trying to use a separate file that contains the search and result vectors the function fails with an Err:504. If I place my search/result sheet within the same workbook and try the Lookup formula again, it will work. This function worked fine in OpenOffice. Sometimes I may get a Err:511. I usually save my files in .xls format but tried .ods format and got the same results.
I've attached sample sheets of what I'm working with. My criterion is from B column in main.xls and my search is from B column in search-result.xls and result is C column of search-result.xls. I've also added a screenshot of the Lookup dialog window showing the error.
*** Bug 33588 has been marked as a duplicate of this bug. ***
Indeed, LOOKUP, HLOOKUP and VLOOKUP all had to be adjusted in order for them to handle external references correctly.
Hopefully this is fixed for good.
This error still has been occurring with this functions and other functions (IF, SUMIF, COUNTIF...) in external references. And all functions created by "Function Wizard" gives error, the external references aren't inserted on the function.
It's fixed in 3.4. Look at the target info.