Function Problems

Apply Excel Conditional Formatting with MATCH Across Worksheets

Muhammad TalhaMuhammad Talha Nov 14, 2026 868 views

Question details

Highlight cells when a value appears in a list on another worksheet.

Apply Excel Conditional Formatting with MATCH Across Worksheets
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.
Before you start

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.

Solution 1Recommended

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.

1
Name the lookup range

In Excel, open Formulas > Define Name. Enter LookupList as the name, set Refers to to =Sheet1!$A$11:$A$700, and select OK.

2
Select the target range

On the worksheet you want to format, select A7:D700, starting with A7. Adjust the range to cover the cells you want highlighted.

3
Create the formula rule

Go to Home > Conditional Formatting > New Rule, choose Use a formula to determine which cells to format, and enter =ISNUMBER(MATCH($A7,LookupList,0)).

4
Choose the formatting

Select Format, choose a fill color or another style, and select OK to save the rule.

Create a MATCH-based conditional formatting rule
Adjust the cell reference: The $ in $A7 keeps the lookup in column A while the row changes. Replace 7 with the first row of your selected range. The rule highlights the selected cells in each matching row.
Work with spreadsheet lookup rules

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. 1. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
  2. 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. 3. Select the target cells: On the target worksheet, select A7:D700, or the range you want to highlight.
  4. 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.
Highlight selected cells based on matches in another worksheet.Apply one formula rule across a target range.Open and edit Microsoft Excel XLSX and XLS files.Use a familiar spreadsheet interface with free WPS Office.
microsoft office alternative - wps office

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.