Ask Your Question

How to search cell value in a list? [closed]

asked 2015-01-31 18:04:37 +0100

cikousek gravatar image

updated 2020-08-15 14:52:48 +0100

Alex Kemp gravatar image

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 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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-15 14:53:04.580626

1 Answer

Sort by » oldest newest most voted

answered 2015-02-01 01:43:43 +0100

m.a.riosv gravatar image

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.

edit flag offensive delete link more


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.

cikousek gravatar imagecikousek ( 2015-02-01 11:47:02 +0100 )edit

@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

m.a.riosv gravatar imagem.a.riosv ( 2015-02-01 15:39:58 +0100 )edit

Question Tools

1 follower


Asked: 2015-01-31 18:04:37 +0100

Seen: 6,896 times

Last updated: Feb 01 '15