Formatting Issues

How to Format Excel Cells Based on Matching Values in Another Sheet

John Wilson Sep 28, 2026 870 views

Question details

The user needs to apply conditional formatting to a column in one worksheet by matching its values with unique identifiers in another worksheet, applying a specific color based on the matched criteria.

How to Format Excel Cells Based on Matching Values in Another Sheet

Product: Excel
Device & OS: not provided
Scenario: Comparing data between two separate sheets to automatically color-code cells in the second sheet depending on the status or label defined in the first sheet.
Observed behavior: Matched cells need to update their background color according to the specific criteria in the reference sheet, while unmatched cells must remain completely unformatted.

Before you start

Ensure both worksheets are within the same workbook and that the reference list contains unique, duplicate-free values to prevent inaccurate formatting results.

Solution 1Recommended

Use XLOOKUP in Conditional Formatting

This is the most straightforward method to match data across different sheets and apply specific formatting rules if you are using a modern version of Excel.

Because conditional formatting formulas cannot directly copy the physical fill color from another cell, you must create a separate rule for each specific text label (e.g., 'Orange', 'Green') found in your reference column.

Select the target range

Open Sheet 2 and highlight the entire range of cells in Column G that you wish to apply the formatting to.

Create a new formatting rule

Navigate to the 'Home' tab on the ribbon, click on 'Conditional Formatting', and select 'New Rule' from the drop-down menu.

Choose the formula option

In the New Formatting Rule dialog box, select 'Use a formula to determine which cells to format'.

Enter the XLOOKUP formula

In the formula bar, enter: =XLOOKUP(G1,'Sheet 1'!$B:$B,'Sheet 1'!$A:$A,"")="Orange". Make sure G1 corresponds to the very first cell in your highlighted selection.

Set the format color

Click the 'Format' button, go to the 'Fill' tab, choose the desired color (e.g., Orange), and click 'OK' twice to apply. Repeat this entire process for any additional colors or statuses.

Cell Referencing: Ensure the first cell reference (G1) does not have dollar signs (absolute references), while the lookup arrays ($A:$A and $B:$B) are locked with dollar signs.

Solution 2

Use INDEX and MATCH Formulas (Alternative Method)

If XLOOKUP is unavailable in your version of Excel, you can use the INDEX and MATCH combination to achieve the exact same cross-sheet matching.

Advanced Spreadsheet Formatting with WPS Office

Seamlessly Format Cells Across Sheets with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas like XLOOKUP, VLOOKUP, and INDEX/MATCH within conditional formatting. You can easily manage complex cross-sheet data visualization in a fast, familiar environment.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your multiple sheets.
2. Access conditional formatting: Highlight your target data column, go to the 'Home' tab, and click 'Conditional Formatting'.
3. Add a formula rule: Select 'New Rule' from the menu and choose the option to enter a custom formula.
4. Input your matching logic: Enter your XLOOKUP or INDEX/MATCH formula to securely reference the data in your other sheet.
5. Customize and apply: Set your desired fill color in the Format options and click 'OK' to instantly update your cells.

Fully compatible with Microsoft Excel (.xlsx) formulas and conditional formattingBuilt-in support for modern functions like XLOOKUP for easier cross-sheet matchingLightweight application with a highly intuitive, tabbed user interfaceFree to use for everyday data analysis and visualization tasks

microsoft office alternative - wps office

Frequently Asked Questions

Can conditional formatting directly copy a cell's background color from another sheet?

No, standard conditional formatting evaluates cell values, not their visual formatting. You must use formulas to evaluate the text or data (like a status word) and apply the corresponding color through the rule's built-in format settings.

Why isn't my conditional formatting formula updating correctly down the column?

This usually happens due to incorrect referencing. Ensure the reference to your current cell (e.g., G1) is relative (contains no $ signs), while the lookup ranges in the reference sheet (e.g., $A:$A) are absolute (locked with $ signs).

Can I use VLOOKUP instead of XLOOKUP for this formatting rule?

Yes, but VLOOKUP only searches from left to right. If your return value (the color label) is located to the left of your lookup value (the unique ID), VLOOKUP will fail unless you restructure your columns. INDEX/MATCH or XLOOKUP handle bidirectional lookups perfectly.

How do I ensure empty cells are ignored and remain unformatted?

If you are strictly matching text values like "Orange" or "Green", blank cells will automatically return FALSE and remain unformatted. If you are matching values that could result in blanks triggering rules, you can wrap your formula in an IF statement or add an ISBLANK check.

Document & File Issues