LibreOffice Calc - Partial Match Column Compare Question

How can I perform a case insensitive partial match from one column to the next? Example.

Column A

green

red

brown

Column B

The green tree

Orange Leaves

Brown Dirt

I would like to search all the phrases in Column B with the words in Column A.

Desired Outcome:

The Phrase “The green tree” in column B should be identified because the word “green” from column A was found.

The Phrase “Brown Dirt” in column B should be identified because the word “brown” from column A was found.

I’ve scoured the web looking for a solution and I haven’t found one that can be easily used without the knowledge of writing code.

What is “identified”? shown in a dialog box when you click a button? appear in a dedicated cell? its cell highlighted on entry?

Identified can mean anything that lets me know there was a match. I.e. Highlighted or a word displayed in an adjacent cell to indicate that there is a match so that I can then work with those entries.

You may use this formula (for B1):

=SUMPRODUCT(ISNUMBER(SEARCH($A$1:$A$3;B1)))

It may be used in an adjacent column (and then the number it shows would indicate how many matches there were), or as a condition in conditional formatting for B column (and then you may highlight the cells with matches).

But keep in mind that, naturally, the formula involves searching for every A-cell text in the B-cell, so it has high computational complexity. Increasing A-range in the formula will quadratically increase computation time.

Hi, The same formula if we need to use for multiple data search in B1 to B200 for data in A1 to A100 how do we code

Thanks so much. This works well. Solved.