Apply Excel Conditional Formatting with MATCH Across Worksheets
Question details
Highlight cells when a value appears in a list on another worksheet.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Apply one conditional formatting rule to a target range using a lookup list on another worksheet.
- Observed behavior
- Matching rows or cells are highlighted, with the tested row changing while the lookup range stays fixed.
Identify the lookup list and the first row and column of the range you want to format. In this example, the lookup list is Sheet1!A11:A700, and the target range starts at A7.
Create a MATCH-based conditional formatting rule
Use a named range and one formula to highlight target cells when the value in column A appears in the lookup list.
In Excel, open Formulas > Define Name. Enter LookupList as the name, set Refers to to =Sheet1!$A$11:$A$700, and select OK.
On the worksheet you want to format, select A7:D700, starting with A7. Adjust the range to cover the cells you want highlighted.
Go to Home > Conditional Formatting > New Rule, choose Use a formula to determine which cells to format, and enter =ISNUMBER(MATCH($A7,LookupList,0)).
Select Format, choose a fill color or another style, and select OK to save the rule.

Highlight matching values in WPS Spreadsheet
Use a named lookup range and a MATCH formula in WPS Spreadsheet to apply conditional formatting across worksheets.
- 1. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
- 2. Name the lookup list: Use Formulas > Define Name to name Sheet1!$A$11:$A$700 as LookupList, adjusting the sheet and range to match your workbook.
- 3. Select the target cells: On the target worksheet, select A7:D700, or the range you want to highlight.
- 4. Add the formatting rule: Choose Home > Conditional Formatting > New Rule, select the formula-based rule, and enter =ISNUMBER(MATCH($A7,LookupList,0)). Choose a format and save the rule.

Frequently Asked Questions
Why is there a dollar sign before A in $A7?
It fixes the lookup column as A while allowing the row number to change for each row in the selected range.
Do I need a separate rule for every row?
No. Select the entire target range before creating the rule. Excel evaluates the formula for each cell using its relative row reference.
Why use a named range for the other worksheet?
A named range lets the conditional formatting formula refer to the lookup list without a direct worksheet reference, which some Excel versions may reject in conditional formatting rules.
How do I change which cells are highlighted?
Go to Home > Conditional Formatting > Manage Rules and edit the Applies to range. Make sure the formula's row number corresponds to the first row of that range.




