Google Sheets：如何高亮在B/C列句子中出现的A列单词？

Got it, let's reverse that conditional formatting logic you had before—here's exactly how to highlight the words in Column A that show up anywhere in Columns B or C:

Step-by-Step Setup #

1. Select the range in Column A you want to apply this to (e.g., A1:A3 if that's your full list of words).
2. Go to Format > Conditional formatting from the top menu.
3. In the conditional formatting sidebar:
   • Under "Format cells if", choose Custom formula is.
   • Paste this formula into the input box:

     =SUMPRODUCT(--REGEXMATCH($B:$C, "\b"&$A1&"\b")) > 0
     

   • Pick your preferred highlight style (fill color, text color, etc.).
4. Click Done to apply the rule.

How the Formula Works #

Let's break down what each part does:

• \b is a regex word boundary—this ensures we match whole words only (so "Sausage" won't accidentally match "Sausages" or "sausagepie"). If you don't need exact whole-word matches, you can remove the \b markers, making the formula =SUMPRODUCT(--REGEXMATCH($B:$C, $A1)) > 0.
• REGEXMATCH($B:$C, "\b"&$A1&"\b") checks every cell in Columns B and C to see if it contains the word from the current row in Column A.
• The -- converts the TRUE/FALSE results from REGEXMATCH into 1/0 numbers.
• SUMPRODUCT adds up all those 1s and 0s. If the total is greater than 0, it means the word from Column A appeared at least once in B/C, so the condition triggers the highlight.

Example Outcome #

Using your sample data:

• A1: Sausage → Highlighted (appears in B1: "I like Sausage")
• A2: Wiener → Not highlighted (no matches in B/C)
• A3: Brat → Highlighted (appears in C1: "I like Brat")

内容的提问来源于stack exchange，提问作者Rattikarl