Ask Your Question
0

[Calc] Find partial match in other cell [closed]

asked 2012-03-15 14:02:36 +0200

anonymous user

Anonymous

Now i'm using INDEX($page2.$A$1:$A$733;MATCH(A1;$page2.$C$1:$C$733;)) to find the corresponding value A1 in page2.C1:C733; it returns a value from A:1A733.

However, some cells in row A containing only a part of the value. How can i find them? For example, A1 is 'john' and one cell in row page2.A1:A733 contains 'john deere'. How do i get a match between john and john deere?

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 2015-10-15 15:26:28.147565

1 Answer

Sort by » oldest newest most voted
1

answered 2012-03-15 22:12:09 +0200

m.a.riosv gravatar image

As MATCH() support regular expressions you can use it

INDEX($page2.$A$1:$A$733;MATCH(A1&".*";$page2.$C$1:$C$733;))

See in the program help for regular expression.

You need to have set the use of regular expressions in Menu/Tools/Options/Calc/calculate.

Also remember, if the file is saved as xls then the set for use of regular expressions is lost and must be set every time the file is open.

edit flag offensive delete link more

Comments

This seems to work for me using LO 4.0 (I'm not sure about the $page2 part -- I just removed that)

qubit gravatar imagequbit ( 2013-02-16 02:49:36 +0200 )edit

Question Tools

Stats

Asked: 2012-03-15 14:02:36 +0200

Seen: 2,219 times

Last updated: Mar 15 '12