LibreOffice Calc - Partial Match Column Compare Question

[IvanWatkins]

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.

[mikekaganski]

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

[IvanWatkins]

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.

[mikekaganski]

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.

[Macho1]

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

[IvanWatkins]

Thanks so much. This works well. Solved.