Formula Errors

How to Apply a Two-Way XLOOKUP Conditional Formatting Rule in Excel

Tauseeq Magsi Sep 25, 2026 869 views

Question details

The user wants to apply a two-way XLOOKUP conditional formatting formula across a range of cells without needing to create separate rules for every individual cell.

How to Apply a Two-Way XLOOKUP Conditional Formatting Rule in Excel

Product: Excel
Device & OS: not provided
Scenario: Setting up conditional formatting that highlights specific cells based on a two-way lookup (matching both a row label and a column date).
Observed behavior: The user needs the rule to dynamically adapt across a specified range so that only the exact intersecting cells are colored, rather than entire rows being incorrectly highlighted.

Before you start

Verify that your version of Excel supports the XLOOKUP function (available in Microsoft 365 and Excel 2021 or newer) before setting up this conditional formatting rule.

Solution 1Recommended

Use Mixed References in the XLOOKUP Formatting Rule

Use a combination of relative, absolute, and mixed references to ensure a single conditional formatting rule dynamically adjusts across your entire target range.

To apply a two-way XLOOKUP across a broad range of cells (such as D15:H58), you must remove any unnecessary IF functions and TRUE/FALSE outputs. The formula must simply evaluate to TRUE or FALSE natively.

The critical step is locking the lookup table ranges completely (absolute references) while allowing the column dates and row labels to adjust properly (mixed references).

Select the target range

Highlight the entire range of cells where you want the conditional formatting to apply, such as D15:H58.

Open the Conditional Formatting menu

Navigate to the Home tab on the ribbon, click on Conditional Formatting, and select New Rule.

Choose the formula option

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

Enter the two-way XLOOKUP formula

Input the formula: =AND(ISBLANK(D15),XLOOKUP($B15,$S$62:$S$66,XLOOKUP(D$5,$V$61:$Z$61,$V$62:$Z$66="x"))). Ensure that D15 and D$5 are relative or mixed so references adjust across the range, while lookup arrays like $S$62:$S$66 remain absolute.

Set the cell format

Click the Format button, choose your desired fill color for the highlighted cells, and click OK to apply the rule.

Use Mixed References in the XLOOKUP Formatting Rule

Reference Check: Using a mixed reference like D$5 locks the row but lets the column change, which is the key to preventing the entire row from being highlighted incorrectly.

Apply Advanced Conditional Formatting Easily in WPS Spreadsheet

WPS Spreadsheet fully supports modern array functions like XLOOKUP, allowing you to build complex two-way conditional formatting rules just as you would in Microsoft Excel. The intuitive interface makes it easy to manage rules across large datasets.

1. Select your data range: Open your workbook in WPS Spreadsheet and highlight the target range (e.g., D15:H58).
2. Access Conditional Formatting: Go to the Home tab, click Conditional Formatting, and choose New Rule.
3. Apply the formula: Select 'Use a formula to determine which cells to format', paste your XLOOKUP formula with correct mixed references, set the format color, and click OK.

Fully compatible with Microsoft Excel formulas, including XLOOKUP and complex AND/OR logicIntuitive Conditional Formatting manager for editing Applies To ranges instantlyFree and lightweight software for high-performance data analysis

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting highlighting the entire row instead of one cell?

This happens when the column reference in your formula is locked as an absolute reference (e.g., $D$5 instead of D$5). By removing the dollar sign before the column letter, the rule evaluates each column individually across the 'Applies to' range.

Can I use XLOOKUP inside conditional formatting?

Yes. As long as your spreadsheet software supports the XLOOKUP function, it can be used within conditional formatting. Just ensure the formula is designed to return a TRUE or FALSE outcome.

Why does my XLOOKUP formula return an error in the formatting rule?

Conditional formatting requires a logical test. If your XLOOKUP formula returns a value (like a number or text) instead of a logical TRUE/FALSE, the formatting won't trigger correctly. Add a logical condition, such as equating the lookup result to a specific value (e.g., ="x"), and remove any unnecessary IF functions.

Excel & Data Problems