如何用Excel公式判断两列含相同数字（一列带文本）并标记格式

核心思路 #

用SEARCH函数检测Sheet2的目标数字序列是否包含在Sheet1对应列的文本中，配合ISNUMBER将匹配结果转为布尔值，以此触发条件格式。

条件格式设置步骤 #

1. 选中Sheet2中需要标记的B列所有单元格
2. 点击「条件格式」→「新建规则」→ 选择「使用公式确定要设置格式的单元格」
3. 输入以下公式：

   =ISNUMBER(SEARCH(B1, Sheet1!$A:$A))
   

4. 点击「格式」按钮，设置填充色为绿色（或字体绿色），确认后完成设置

公式说明 #

• SEARCH(B1, Sheet1!$A:$A)：在Sheet1的A列所有单元格中查找当前B1的内容，找到则返回匹配位置的数字，找不到返回错误值
• ISNUMBER(...)：将SEARCH的结果转为TRUE（找到）或FALSE（未找到），TRUE时触发条件格式

优化建议 #

• 若Sheet1的A列数据量较大，建议将Sheet1!$A:$A改为具体的单元格范围（如Sheet1!$A$1:$A$2000），提升运算速度
• 如果需要避免部分匹配（比如B列是31383_1，不想匹配A列的31383_1.1），可以调整公式为：

  =SUMPRODUCT(--(ISNUMBER(SEARCH(" " & B1 & " ", " " & Sheet1!$A:$A & " ")))>0)
  

  这个公式通过给匹配内容前后加空格，限定匹配完整的序列（适用于序列前后有空格的情况）

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