Formatting Issues

How to Use Excel Conditional Formatting with VLOOKUP and Range Check

Guest Writer Sep 30, 2026 869 views

Question details

The user needs to highlight a specific cell if its value falls outside a minimum and maximum range, which is retrieved from another worksheet based on an identifier.

How to Use Excel Conditional Formatting with VLOOKUP and Range Check

Product: Excel
Device & OS: not provided
Scenario: Comparing data values against acceptable minimum and maximum boundaries stored in a separate reference table on another sheet.
Observed behavior: The target cell needs to automatically change its formatting (e.g., turn red) when the inputted value is outside the allowed boundaries defined by the lookup table.

Before you start

Before setting up your conditional formatting rule, ensure that your reference lookup table on the other worksheet is properly structured, with the lookup identifiers in the first column and the minimum and maximum values in separate, identifiable columns.

Solution 1Recommended

Apply Conditional Formatting with OR and VLOOKUP Functions

Use a custom formula combining OR and VLOOKUP functions to dynamically check a cell's value against a defined minimum and maximum range in another sheet.

This method uses the OR function to check two conditions: whether the cell is smaller than the minimum value, or greater than the maximum value. If either condition is true, the formatting is applied. VLOOKUP is used to pull those min and max boundaries from your reference table.

Select the target cell

Select the cell or range of cells (for example, AE6) that you want to apply the formatting to.

Open the Conditional Formatting menu

Navigate to the Home tab on the Excel ribbon, click on 'Conditional Formatting', and select 'New Rule' from the dropdown menu.

Enter the custom formula

Choose 'Use a formula to determine which cells to format'. Enter a formula exactly like this: =OR(AE6<VLOOKUP(Bulk_SAP_cell,lookup_range,3,FALSE),AE6>VLOOKUP(Bulk_SAP_cell,lookup_range,4,FALSE)). Make sure to replace 'Bulk_SAP_cell' with the cell containing your lookup value and 'lookup_range' with the actual range of your reference table.

Set the format and save

Click the 'Format' button, go to the 'Fill' tab, choose a highlight color such as red, click 'OK' to confirm the color, and click 'OK' again to save and apply the rule.

Apply Conditional Formatting with OR and VLOOKUP Functions

Formula Parameters: In the provided VLOOKUP formula, '3' and '4' represent the column index numbers for your minimum and maximum values respectively. You will need to adjust these numbers based on the actual column positions in your reference table.

Effortlessly Manage Conditional Formatting with WPS Spreadsheet

WPS Spreadsheet fully supports complex conditional formatting rules, including custom formulas combining logical operators and lookup functions, allowing you to highlight out-of-range data seamlessly.

1. Select the target range: Highlight the cells you want to check against your reference data in your WPS Spreadsheet workspace.
2. Access conditional formatting: Go to the Home tab, click on Conditional Formatting, and select New Rule.
3. Input the VLOOKUP formula: Select 'Use a formula to determine which cells to format', enter your combined OR and VLOOKUP formula, set a prominent fill color, and click OK.

Fully compatible with Microsoft Excel formulas and conditional formatting rules.Intuitive interface for setting up cross-sheet data validation.Free, lightweight, and professional alternative for advanced data analysis.

microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my conditional formatting VLOOKUP formula working across different worksheets?

Ensure your lookup range from the other worksheet uses absolute references (e.g., Sheet2!$A$1:$D$100). If you use relative references, the range will shift as the rule is evaluated, causing it to return incorrect results or errors.

Can I use XLOOKUP instead of VLOOKUP for this conditional format?

Yes, if your version of Excel supports XLOOKUP, you can replace VLOOKUP with XLOOKUP to fetch the minimum and maximum boundaries. XLOOKUP is often more robust as it doesn't rely on static column index numbers that might break if you insert new columns.

How do I highlight cells that fall exactly within the minimum and maximum range instead?

Instead of the OR function, you can use the AND function in your conditional formatting formula. For example: =AND(AE6>=VLOOKUP(Bulk_SAP_cell,lookup_range,3,FALSE), AE6<=VLOOKUP(Bulk_SAP_cell,lookup_range,4,FALSE)).

Does WPS Spreadsheet support conditional formatting that references another sheet?

Yes, WPS Spreadsheet fully supports referencing other sheets within conditional formatting formulas. You can seamlessly apply rules that pull reference limits or rules from any sheet within your workbook.

Excel & Data Problems