Ask Your Question

If part of cell is equal part in array, return equal part in CALC

asked 2019-03-31 23:32:27 +0100

abrj gravatar image

updated 2019-03-31 23:34:23 +0100

I have some combined text and numbers in column A, and in column B an Array of numbers. In column C I check if there is a part of the text in column A that's equal to the array.
Now it returns a Yes in column C if some part is equal, but I want to return the part of the string in column A that's equal to the part in column B.
How do I do that in an easy way?

In excel I've managed to make an formula that works (I put it in column D), but in Calc it returns only blank cells. The Excel formula is

I've added a demofile here; C:\fakepath\democalc.ods

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2019-04-01 00:53:55 +0100

m.a.riosv gravatar image

What LibreOffice version are you using?, I think it was solved no too much ago, and works for me with 6.2.3, please test updating.

edit flag offensive delete link more


Yes, it works only as of 6.2, see also tdf#117016.

erAck gravatar imageerAck ( 2019-04-03 12:59:33 +0100 )edit

answered 2019-04-02 12:20:29 +0100

abrj gravatar image

You solved my problem with your demofile, thank you!

edit flag offensive delete link more


Please, if the answer solves the question click ✔.

m.a.riosv gravatar imagem.a.riosv ( 2019-04-02 12:21:40 +0100 )edit

answered 2019-04-01 02:51:34 +0100

Lupp gravatar image

Sorry. Could someone, please, explain the number 2 in the first parameter position of LOOKUP to me? It should be the number 1. I also cannot clearly find a justification in OpenFormula for ignoring error values in the SearchVector. The results I just got in my tests didn't produce a consistent picture.

See also this attached example. It is demonstrating a few solutions avoiding the above mentioned problems. There is also allowed for more than one match per c ell content in column A.

edit flag offensive delete link more



Because it's greater than any valid result of 1/ISNUMBER(FIND($B$2:$B$28;A2)) and without the last parameter for LOOKUP it finds the last value that matches.. Lookup value unsorted data.

m.a.riosv gravatar imagem.a.riosv ( 2019-04-01 17:28:48 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-03-31 23:32:27 +0100

Seen: 38 times

Last updated: Apr 02 '19