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;
democalc.ods

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.

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

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.

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.

You solved my problem with your demofile, thank you!

Please, if the answer solves the question click :heavy_check_mark:.