Ask Your Question
0

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

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

abrj gravatar image

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

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
=IFERROR(LOOKUP(2;1/ISNUMBER(FIND($B$2:$B$28;A2));$B$2:$B$28);"")

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
0

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

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

Comments

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

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

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

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

Comments

1

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 +0200 )edit
0

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

abrj gravatar image

You solved my problem with your demofile, thank you!

edit flag offensive delete link more

Comments

Please, if the answer solves the question click ✔.

m.a.riosv gravatar imagem.a.riosv ( 2019-04-02 12:21:40 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 24 times

Last updated: Apr 02