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

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?

delete close flag offensive retag edit

1 Answer

Sort by » oldest newest most voted

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

mariosv

4629 ● 20 ● 46

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.

link delete flag offensive edit

Comments

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

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

Resources

Donate

LibreOffice is made available by volunteers around the globe, backed by a charitable Foundation. Please support our efforts: Your donation helps us to deliver a better product!

Question tools

Stats

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

Seen: 538 times

Last updated: Mar 15 '12

[Calc] Find partial match in other cell