How to search cell value in a list?

[cikousek]

Hi guys,

I don´t know how to make CALC search for a cell in an array. The problem is that it is possible the searched values are not exactly the same (e.g. search for “22” and find “22”, “test 22”, “22 test” etc. as if you´d find the results using ctrl+f). In Excel I used MATCH(" * “&B2&” * ";M1:M55;0) (no spaces between " and * of course) to find the row the cell´s value is in. Unfortunately, the CALC 4.3.5.2 doesn´t accept " * “&B2&” * " or anything similar or the . together with * that I found in some other topic or I don´t know how to put it all together to make it work. Could you please advise, how to force CALC to find the value if it is also a part of the cell value and not just exact match? It could be text, number as well as combination of text and numbers.

Thanks in advance for any advice.

[mariosv]

In calc there are regular expressions to do it, a bit more complex but really powerful.

There is an option in how the search works:
Menu/Tools/Options/LibreOffice calc/General/Calculate - Search criteria = and <> must apply to the whole cell.
If enable: MATCH(".*"&B2&".*";M1:M55;0)
if disable: MATCH(B2;M1:M55;0)

and just below an option to apply or not regular expressions.

[cikousek]

Thanks a lot mariosv! I found the list of regular expressions too, but didn´t enable them. So that solved one problem and another occured.

It works if I use the function within the same file, but when I want to MATCH from different file, it returns #N/A. Both files have regular expressions enabled.

So it works with MATCH(". *"&B2&". *";source.$M$1:$M$16;0) but not with MATCH(". *"&B2&". *";‘file:///C:/…/source.ods’#$source.$M$1:$M$16;0).

Any idea what am I doing wrong?

Thank you.

[mariosv]

@cikousek, seems that really a bug using regular expression in MATCH with linked files, also happend with VLOOKUP. I have just reported the bug tdf#89013