Formula Errors

Fix XLOOKUP Error in Excel Conditional Formatting for Missing Codes

Bushra Parveen Sep 30, 2026 869 views

Question details

The user wants to highlight missing codes using Conditional Formatting, but the XLOOKUP formula is rejected even though it works in standard cells.

How to Fix XLOOKUP Errors in Excel Conditional Formatting When Finding Missing Codes

Product: Microsoft Excel
Device & OS: not provided
Scenario: Applying a conditional formatting rule to identify and highlight items that do not exist in another table or list.
Observed behavior: The XLOOKUP formula and structured table references evaluate correctly in standard worksheet cells but trigger an error when entered into the conditional formatting rule manager.

Before you start

Ensure both your source data and the lookup table are in the same workbook. It is helpful to test your formula in a standard worksheet cell first to verify the logic before applying it to the Conditional Formatting rule manager.

Solution 1Recommended

Replace XLOOKUP and Structured Tables with MATCH and Regular Ranges

Excel's conditional formatting often struggles with newer functions like XLOOKUP and structured table references. Using a classic MATCH function with a standard worksheet range resolves the issue.

While XLOOKUP is highly versatile in standard worksheets, the conditional formatting engine in Excel can reject it, especially when combined with structured table references (e.g., Table[Column]). Reverting to traditional lookup formulas like MATCH combined with ISERROR ensures compatibility.

Select the target range

Highlight the range of cells in your worksheet where you want the conditional formatting to apply (e.g., D5:D100).

Open Conditional Formatting

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

Choose the formula option

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

Enter the MATCH formula

Input the formula: =ISERROR(MATCH(D5,Sheet2!$D:$D,FALSE)) — replacing 'D5' with the top-left cell of your selected range, and 'Sheet2!$D:$D' with the actual worksheet range containing your master code list.

Apply formatting

Click the Format button to choose your desired highlight color (such as a red fill), click OK, and then OK again to apply the rule.

Replace XLOOKUP and Structured Tables with MATCH and Regular Ranges

Relative vs. Absolute References: Make sure your lookup column uses absolute references (like $D:$D) while the criteria cell uses a relative or mixed reference (like D5 or $D5) so the rule applies correctly across your selected range.

Easily Find Missing Data with WPS Spreadsheet

Use Conditional Formatting Seamlessly in WPS Office

WPS Spreadsheet offers robust support for conditional formatting and advanced lookup functions, allowing you to quickly identify missing codes and analyze data without complex workarounds.

1. Open your file in WPS Spreadsheet: Launch WPS Office, open your spreadsheet, and select the data range you want to format.
2. Access Conditional Formatting: Navigate to the Home tab on the ribbon, click on Conditional Formatting, and select New Rule.
3. Select formula rule type: Choose the option 'Use a formula to determine which cells to format' from the list of rule types.
4. Input the formula: Type the formula =ISERROR(MATCH(D5,Sheet2!$D:$D,FALSE)) into the input box.
5. Set format and apply: Click Format to select a distinct background color, then click OK to instantly highlight all missing codes in your dataset.

Fully compatible with Microsoft Excel formats (.xlsx, .xls) and formulas.Intuitive Conditional Formatting manager for effortless data highlighting.Reliable calculation engine for handling complex logical and lookup formulas.Free, lightweight, and easy-to-use alternative to Microsoft Office.

Frequently Asked Questions

Why does XLOOKUP work in a cell but not in conditional formatting?

Excel's Conditional Formatting engine is older and sometimes does not fully support newer array-based functions like XLOOKUP, or specific structured table references across different sheets. Using older, simpler functions like MATCH or VLOOKUP is generally more reliable in these specific dialog boxes.

Can I use structured table references in conditional formatting?

Referencing an Excel Table by its structured name (e.g., Table1[Code]) directly inside the rule often causes an error. To bypass this, you can use the INDIRECT function, define a named range for that specific table column, or simply use regular absolute cell references (e.g., $A$2:$A$100).

How do I conditionally format values that DO exist in another list?

You can use a similar approach but check for numbers instead of errors. Using the formula =ISNUMBER(MATCH(A2,Sheet2!$A:$A,0)) in the conditional formatting rule will highlight all cells in your current range that are successfully found in the other list.

Excel & Data Problems