Google Sheets条件格式规则求助：跨表值匹配时单元格变色

Hey there, let's get your conditional formatting sorted out. You want any cell in B2:AF120 on your current sheet to change background color if its value already exists in Sheet2!G3:Q58—I see you tried a formula with MATCH, but let's tweak it to work properly.

First, quick note: your formula uses Teams!G3:Q58 instead of Sheet2!G3:Q58—make sure you use the correct sheet name here (whether it's Sheet2 or Teams) to avoid errors.

Here's the step-by-step fix: #

• Select your target range: Click and drag to highlight B2:AF120 on your active worksheet.
• Open Conditional Formatting rules: Head to the Home tab → Conditional Formatting → New Rule → Pick "Use a formula to determine which cells to format".
• Enter the working formula:

  =ISNUMBER(MATCH(B2, INDIRECT("Sheet2!G3:Q58"), 0))
  

  Let me break this down:
  • B2 is the starting cell of your range—Excel will automatically adjust this for every cell in B2:AF120 (so it checks C2, D2, etc., as it goes).
  • MATCH(B2, INDIRECT("Sheet2!G3:Q58"), 0) looks for an exact match of the cell's value in the other sheet's range.
  • ISNUMBER turns the match result into a TRUE/FALSE value that conditional formatting can use (TRUE means the value exists, so we format the cell).
  • If your sheet is actually named Teams (not Sheet2), just swap Sheet2 with Teams inside the INDIRECT function.
• Set your desired background color: Click the "Format" button, go to the Fill tab, pick your color, then hit OK twice to apply the rule.

Why your original formula didn't work #

Your original formula =match(B2:AF120,indirect("Teams!G3:Q58"),0) tries to match an entire range at once, but conditional formatting expects a formula that references a single cell (like B2) to iterate over the selected area. Using the full range B2:AF120 in MATCH creates an array mismatch that Excel can't process correctly for this use case.

Quick pro tip: If you want to avoid using INDIRECT (which can be finicky if sheet names change), you can define a named range for Sheet2!G3:Q58 (say, ExistingValues) and use =ISNUMBER(MATCH(B2, ExistingValues, 0)) instead—it's cleaner and less error-prone.

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