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?
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.
This seems to work for me using LO 4.0 (I’m not sure about the $page2 part – I just removed that)