Can MATCH find a string in a different file using regular expression?

I’m using LibreOffice Calc Version: (x64) Build ID: 2196df99b074d8a661f4036fca8fa0cbfa33a497

Tried to locate a column in a different file with the MATCH function using a regular expression for further processing with OFFSET but got “#N/A” instead of a position value. The option "enable regular expressions in formulas is switched on for both files.

To reproduce the problem I created two simplified files:

The cells with a MATCH function for the file TestMATCHdata.ods show “#N/A”.

This is the first time, that I place a question. So if I should prepare it in a better way, please let me know. I tried to search for this problem. If this is “works as designed” or a known bug, please let me know as well. I don’t want to spoil your time with questions which are already answered.(Is it possible to delete the files /upfiles/16127844378870018.ods and /upfiles/1612784609101015.ods as they contain the wrong file path in the formulas?)

Thank you so much for your effort.

[Edit - Opaque] Made /upfiles/16127852609473133.ods a real link to a file and marked strikethrough what I understand OP assumes to be incorrect sample files.

Hint aside: Using function FORMULA() will save you a lot of copy&paste effort when preparing test cases :wink:

Thanks for your hint. You are right, even though I must confess, that after reading the help for the FORMULA function, it took me some seconds to understand, which part of my copy&paste effort I could have saved. :slight_smile:


this should work, but it doesn’t and there is a bug report tdf#89013 Regular expressions doesn’t work with MATCH and VLOOKUP when searched range is in other file

Thanks a lot for pointing me newbie to the bug report. Next time I know better, where to search for known bugs. Sorry to have bothered the community with such a known bug.

Sorry to have bothered the community with such a known bug.

No need to excuse - and sometimes it is hard to find a problem’s related bug report.

*If you consider your question answered, it would be nice, if you click the check mark (✔) next to the answer. Thanks in advance ...*

Thanks for this info. Now I know how to say, that I got a proper answer. :slight_smile:
Next time it will be faster.