Search code examples
excelexcel-formulaconditional-formatting

Highlight cell if any value matches another column

I have 2 columns in a spreadsheet. Column A represents a list of body parts, Column B represents a list of Exams.

Column 1:

ABDOMEN
HIP
RIGHT ARM
LEFT LEG

Column 2:

XRAY LEFT LEG
US ABDOMEN
MR BREAST
XRAY HIP
MRI RIGHT ARM

What I am trying to do is highlight any cell in Column 2 if any of the text contains a match in Column 1. So for example, ABDOMEN in Column 1 would highlight US ABDOMEN in Column 2; MR BREAST in Column 2 would not be highlighted as there is no match in Column 1.

Ive tried several variations of MATCH or COUNTIF, but come up with either no matches, or incorrect matches. Another problem is some of my text in Column 1 contains spaces, and I would need it to match the text exactly as it shows in Column 1 (ex. MRI RIGHT ARM would be a match; MRI RIGHT would not; MRI ARM would not).

=COUNTIF(B:B,A1)=1

Highlights nothing.

Solution

  • You could try using the following in Conditional Formatting:

    enter image description here

    • Using SUM() + ISNUMBER() + SEARCH()

    =SUM(N(ISNUMBER(SEARCH(" "&A$1:A$4&" "," "&B1&" "))))

    • Using TEXTBEFORE() + ISNA()

    =1-ISNA(TEXTBEFORE(B1,A$1:A$4))