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

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 close merge delete

Sort by » oldest newest most voted

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.

more

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

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

You solved my problem with your demofile, thank you!

more

Please, if the answer solves the question click ✔.

( 2019-04-02 12:21:40 +0100 )edit

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.

more

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.

( 2019-04-01 17:28:48 +0100 )edit

## Stats

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

Seen: 38 times

Last updated: Apr 02 '19