Function Problems

How to Apply Excel Conditional Formatting Based on a Lookup from Another Sheet

Maira Mehtab Sep 20, 2026 868 views

Question details

The user needs to dynamically format specific cells based on whether their values exceed a defined threshold that must be looked up from a separate worksheet.

Product: Spreadsheets
Device & OS: not provided
Scenario: Highlighting data points in a primary sheet (e.g., Claim Age) by comparing them to corresponding benchmark values located in a secondary sheet (e.g., StateGrid).
Observed behavior: Cells need to automatically change formatting, such as font color or background fill, whenever their values meet the condition evaluated against a lookup table on another sheet.

Before you start

Ensure that the lookup table in your secondary sheet is properly structured, with the lookup key (like the state name) in the first column and the threshold values in subsequent columns.

Solution 1Recommended

Use VLOOKUP in Conditional Formatting

Apply a formula-based conditional formatting rule that uses the VLOOKUP function to compare values across different sheets and apply custom styles.

Excel's Conditional Formatting tool allows you to use custom formulas to trigger formatting changes. By embedding a VLOOKUP function inside the conditional formatting formula, you can seamlessly reference and evaluate data from another worksheet without needing helper columns.

Select the target cells

Highlight the range of cells you want to format in your primary sheet, such as the 'Age of Claim' cells.

Open Conditional Formatting menu

Navigate to the Home tab, click on 'Conditional Formatting' in the Styles group, and select 'New Rule'.

Choose the formula option

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

Enter the VLOOKUP formula

Enter a lookup formula comparing your target cell to the lookup result. For example: =A2>VLOOKUP(B2,StateGrid!A:B,2,0). Make sure to adjust the cell references, column indices, and sheet names to exactly match your workbook structure.

Set the format and apply

Click the 'Format' button, choose your desired fill color or font color, click 'OK', and then click 'OK' again to apply the rule.

Relative vs Absolute References: Pay close attention to absolute ($A$2) and relative (A2) references in your conditional formatting formula. If you want the rule to apply across an entire column, ensure the row number is relative (e.g., A2 instead of $A$2) so it adjusts for each row.

Professional Spreadsheet Tool

Easily Manage Cross-Sheet Conditional Formatting with WPS Spreadsheet

WPS Spreadsheet provides robust support for complex formula-based conditional formatting, including VLOOKUP across multiple sheets. Manage large datasets efficiently with its intuitive formatting rules manager.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the data sheets.
2. Select the data range: Highlight the cells in the primary sheet that require formatting.
3. Apply conditional formatting: Go to the Home tab, click 'Conditional Formatting', and select 'New Rule'.
4. Enter the lookup formula: Select 'Use a formula to determine which cells to format', input your VLOOKUP formula, define your custom format, and click 'OK'.

Fully compatible with Microsoft Excel (.xlsx) conditional formatting rules and formulas.User-friendly rule manager to easily edit and oversee multiple formatting conditions.Lightweight performance ensuring fast calculations even with extensive cross-sheet lookups.Free to use with comprehensive spreadsheet analysis tools built-in.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use XLOOKUP or INDEX/MATCH instead of VLOOKUP in conditional formatting?

Yes, as long as the function returns a value that can be logically evaluated (e.g., > or <), you can use modern functions like XLOOKUP or INDEX/MATCH inside the conditional formatting formula.

Why isn't my conditional formatting updating when the lookup value changes?

This usually happens if your workbook's Calculation Options are set to Manual. Go to the Formulas tab and ensure Calculation Options are set to Automatic. Also, double-check that your formula's cell references are correctly structured as relative or absolute.

How do I highlight the entire row based on a lookup value from another sheet?

To highlight an entire row, you must lock the column reference in your conditional formatting formula by using a dollar sign before the column letter (e.g., =$A2>VLOOKUP($B2,StateGrid!A:B,2,0)). Then, ensure the 'Applies to' range covers the entire dataset rather than just a single column.

Excel & Data Problems