Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSun, 01 Feb 2015 15:39:58 +0100How to search cell value in a list?https://ask.libreoffice.org/en/question/45589/how-to-search-cell-value-in-a-list/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.Sat, 31 Jan 2015 18:04:37 +0100https://ask.libreoffice.org/en/question/45589/how-to-search-cell-value-in-a-list/Answer by m.a.riosv for <p>Hi guys,</p>
<p>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.</p>
<p>Thanks in advance for any advice.</p>
https://ask.libreoffice.org/en/question/45589/how-to-search-cell-value-in-a-list/?answer=45600#post-id-45600In calc there are [regular expressions](https://help.libreoffice.org/3.3/Common/List_of_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.Sun, 01 Feb 2015 01:43:43 +0100https://ask.libreoffice.org/en/question/45589/how-to-search-cell-value-in-a-list/?answer=45600#post-id-45600Comment by cikousek for <p>In calc there are <a href="https://help.libreoffice.org/3.3/Common/List_of_Regular_Expressions">regular expressions</a> to do it, a bit more complex but really powerful.</p>
<p>There is an option in how the search works: <br/>
Menu/Tools/Options/LibreOffice calc/General/Calculate - Search criteria = and <> must apply to the whole cell. <br/>
If enable: <code>MATCH(".*"&B2&".*";M1:M55;0)</code> <br/>
if disable: <code>MATCH(B2;M1:M55;0)</code> </p>
<p>and just below an option to apply or not regular expressions.</p>
https://ask.libreoffice.org/en/question/45589/how-to-search-cell-value-in-a-list/?comment=45618#post-id-45618Thanks 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.Sun, 01 Feb 2015 11:47:02 +0100https://ask.libreoffice.org/en/question/45589/how-to-search-cell-value-in-a-list/?comment=45618#post-id-45618Comment by m.a.riosv for <p>In calc there are <a href="https://help.libreoffice.org/3.3/Common/List_of_Regular_Expressions">regular expressions</a> to do it, a bit more complex but really powerful.</p>
<p>There is an option in how the search works: <br/>
Menu/Tools/Options/LibreOffice calc/General/Calculate - Search criteria = and <> must apply to the whole cell. <br/>
If enable: <code>MATCH(".*"&B2&".*";M1:M55;0)</code> <br/>
if disable: <code>MATCH(B2;M1:M55;0)</code> </p>
<p>and just below an option to apply or not regular expressions.</p>
https://ask.libreoffice.org/en/question/45589/how-to-search-cell-value-in-a-list/?comment=45621#post-id-45621@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](https://bugs.documentfoundation.org/show_bug.cgi?id=89013)Sun, 01 Feb 2015 15:39:58 +0100https://ask.libreoffice.org/en/question/45589/how-to-search-cell-value-in-a-list/?comment=45621#post-id-45621