# 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.

edit retag close merge delete

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

( 2019-01-01 12:01:41 +0200 )edit

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.

( 2019-01-01 12:55:29 +0200 )edit

Sort by » oldest newest most voted

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.

more

Thanks so much. This works well. Solved.

more