how to match a cell when the search region is in a different document

[arya]

How do I match a cell in one document to a range of cells in another document?

For example, the following works when the search item and search region are in the same document:

=IFERROR(MATCH($A2,D$2:D$500,0),"")

…It correctly displays the line number of the first occurrence of the matching cell.

However, the following does NOT work when the search item and search region are in different documents:

=IFERROR(MATCH($A2,‘file:///var/www/html/Projects/reports/Complete_All.xlsx’#$Complete_All.$A$2:$A$500,0),"")

I am using LibreOffice Calc version 6.7.0.3.

Reference: Documentation/How Tos/Calc: MATCH function - Apache OpenOffice Wiki

---

UPDATE: 2019-10-10
Based on comments, I saved both files as XLSX and checked the cell references. The issue still exists. Here are both the files:

Source File test_a.xlsx

*Column A has the source/needle cells

*Column B has the MATCH formula

Range File test_b.xlsx

*Column A has the range/haystack cells

[gregors15]

Hi, would you be able to upload a sample of the file your linking out to?

[arya]

Yes, I uploaded both files.

[gregors15]

Hi, can you create a new column in the file with the match (test_a) and do Numbervalue(A1) and use this column to do the match.

[mariosv]

I have just tested and it works for me with 6.3, verify the file reference is right. Open the csv file and with both open side by side modify the refence to the csv file.

edited 20191010

1. the data in column A of the file with MATCH is as text not as numbers.

2. There is an old bug report about the broken links with xlsx files.
   FILESAVE: Links to external data not preserved for XLSX