Excel两列跨行匹配值高亮需求技术问询

Hey there! Let's walk through exactly how to set up conditional formatting in Excel to highlight cross-column duplicates that are on different rows—ignoring same-row matches like you requested.

1. Select Your Target Range #

First, highlight all cells in both Column 1 and Column 2 that contain your data (skip headers if you have them, or adjust the formula later if you want to include them). For example, if your data starts at row 2 and goes to row 100, select A2:B100.

2. Open the Conditional Formatting Rule Builder #

• Go to the Home tab in Excel's ribbon
• Click Conditional Formatting → New Rule
• From the list of rule types, select "Use a formula to determine which cells to format"

3. Paste the Custom Formula #

Enter this formula into the input box (adjust column letters if your columns aren't A and B):

=IF(COLUMN()=1, COUNTIF($B:$B, A1)>0 AND A1<>B1, COUNTIF($A:$A, B1)>0 AND A1<>B1)

Let's break down what this does: #

• COLUMN() checks if the current cell is in Column 1 (returns 1) or Column 2 (returns 2)
• COUNTIF($B:$B, A1)>0 (for Column 1 cells): Verifies that the value in Column 1 exists somewhere in Column 2
• COUNTIF($A:$A, B1)>0 (for Column 2 cells): Verifies that the value in Column 2 exists somewhere in Column 1
• A1<>B1 ensures we ignore same-row matches (so if Column 1 and Column 2 have the same value in the same row, it won't get highlighted)

4. Set Your Highlight Format #

Click the Format button, choose your preferred highlight style (like a yellow fill or bold red text), then click OK twice to apply the rule.

Let's say you have this data:

• Row 1: Column1=Apple, Column2=Banana → No highlight (no cross-column match)
• Row 2: Column1=Orange, Row3 Column2=Orange → Both cells get highlighted (match across different rows)
• Row 4: Column1=Grape, Column2=Grape → No highlight (same-row match, which we exclude)

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