Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

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);"")

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);"")=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