跨工作表表格对比的条件格式配置：缺失行高亮与数据差异高亮需求

Got it, let's break down your two conditional formatting rules step by step—since your tables are unsorted, we need to use multi-column matching instead of simple cell comparisons. Here's how to set up each rule correctly, using your sample data as reference:

Rule 1: Highlight Cells in Table1's First 3 Columns That Don't Exist in Table2 #

This rule checks if the combination of Column1+Column2+Column3 in the current Table1 row has no match in Table2. We'll use COUNTIFS to count matching multi-column rows—if the count is 0, we highlight the cells.

Formula to Use #

If your Table1 data starts at row 2 (with headers in row 1), select all data cells in Table1's Column1 to Column3 (e.g., A2:C7), then create a new conditional formatting rule with this formula:

=COUNTIFS(Sheet2!$A:$A, $A2, Sheet2!$B:$B, $B2, Sheet2!$C:$C, $C2)=0

How It Works #

• Sheet2!$A:$A, Sheet2!$B:$B, Sheet2!$C:$C refer to the three columns in Table2 (lock the columns with $ so the range doesn't shift when the rule applies to other rows).
• $A2, $B2, $C2 refer to the current row's values in Table1's three columns (lock the column, leave the row relative so it adapts to each row in the selection).
• When the count equals 0, it means this row's three-column combo doesn't exist in Table2—triggering the highlight.

Rule 2: Highlight Table1's Column4 Cells That Don't Match the Corresponding Row in Table2 #

This rule first confirms the three-column combo exists in Table2, then checks if Table1's Column4 value differs from the matching row's Column4 in Table2.

Formula to Use #

Select all data cells in Table1's Column4 (e.g., D2:D7), then create a new conditional formatting rule with this formula:

=AND(
    COUNTIFS(Sheet2!$A:$A, $A2, Sheet2!$B:$B, $B2, Sheet2!$C:$C, $C2)>0,
    INDEX(Sheet2!$D:$D, MATCH($A2&$B2&$C2, Sheet2!$A:$A&Sheet2!$B:$B&Sheet2!$C:$C, 0))<>$D2
)

How It Works #

• The AND function ensures two things are true:
  1. COUNTIFS(...)>0: The three-column combo exists in Table2 (avoids errors from MATCH if no match is found).
  2. INDEX(...)<>$D2: We concatenate the three columns ($A2&$B2&$C2) to create a unique key, use MATCH to find its position in Table2's concatenated three columns, then INDEX pulls the corresponding Column4 value from Table2. We compare this to Table1's Column4 ($D2)—if they don't match, we highlight the cell.

Bonus: Use Structured References (If Using Excel Tables) #

If you're using official Excel Tables (not just ranges), structured references are more robust (they auto-expand if you add rows):

• For Rule 1:

  =COUNTIFS(Table2[Column1], @Table1[Column1], Table2[Column2], @Table1[Column2], Table2[Column3], @Table1[Column3])=0
  

• For Rule 2:

  =AND(
      COUNTIFS(Table2[Column1], @Table1[Column1], Table2[Column2], @Table1[Column2], Table2[Column3], @Table1[Column3])>0,
      INDEX(Table2[Column4], MATCH(@Table1[Column1]&@Table1[Column2]&@Table1[Column3], Table2[Column1]&Table2[Column2]&Table2[Column3], 0))<>@Table1[Column4]
  )
  

Why Your Earlier MATCH Attempt Failed #

Chances are you tried matching a single column instead of combining all three—without the combined key, MATCH can't account for the full row context, especially with unsorted tables.

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