Function Problems

How to Combine IF and XLOOKUP with Conditional Formatting in Excel

Ayan Masood Oct 23, 2026 868 views

Question details

The user needs to construct a formula that returns custom text based on an IF condition or performs an XLOOKUP as a fallback, and then automatically formats that custom text in red.

How to Combine IF and XLOOKUP with Conditional Formatting in Excel

Product: Excel
Device & OS: not provided
Scenario: Evaluating a specific cell's truth value to either return a specific flagged text or look up data from another worksheet, while applying visual distinction to the flagged result.
Observed behavior: Requires step-by-step guidance to properly nest an XLOOKUP function within an IF statement using correct logical syntaxes, followed by applying a conditional formatting rule.

Before you start

Ensure your lookup ranges in the destination sheet (e.g., 'Training Data') are perfectly aligned and do not contain hidden errors or mismatched rows before building complex formulas.

Solution 1Recommended

Combine IF and XLOOKUP Functions and Apply Conditional Formatting

Embed an XLOOKUP formula into the 'Value if False' argument of an IF statement, and utilize highlight cell rules to format the output text.

In Excel, logical values like TRUE or FALSE do not require quotation marks. Enclosing TRUE in quotation marks converts it to a text string, which can cause formula logic to fail.

XLOOKUP is highly versatile as a fallback function inside an IF formula. It allows you to specify a 'not found' value directly inside the formula, bypassing the need for nested IFERROR functions.

Write the Combined Formula

Select your target cell and enter the formula: =IF(AA10=TRUE,"TRUE*",XLOOKUP($F10&"Module 1: Product Overview - MPIE",'Training Data'!$A$1:$A$9738,'Training Data'!$J$1:$J$9738,"",0)). Press Enter to apply.

Access Conditional Formatting

With the cell containing your formula selected, navigate to the 'Home' tab on the top ribbon, click on 'Conditional Formatting', and hover over 'Highlight Cells Rules'.

Set the Formatting Rule

Click 'Equal To' from the dropdown menu. In the text box, type TRUE* (matching your formula's exact output). In the formatting dropdown, select 'Red Text' or choose 'Custom Format' to manually set the font color to red. Click 'OK'.

Combine IF and XLOOKUP Functions and Apply Conditional Formatting

Seamless Automation: Now, whenever the source cell evaluates to TRUE, your combined formula will output TRUE* and automatically display it in red without any manual text highlighting.

Advanced Spreadsheets

Build Complex Formulas Seamlessly with WPS Office

WPS Spreadsheet fully supports the latest modern logical and lookup functions including XLOOKUP and IF, alongside robust Conditional Formatting capabilities to streamline your data analysis workflows.

1. Open Your Spreadsheet: Launch WPS Office and open your .xlsx data workbook.
2. Enter the Formula: Select the target cell, input the combined IF and XLOOKUP formula, and press Enter.
3. Apply Cell Rules: Navigate to 'Home' > 'Conditional Formatting' > 'Highlight Cells Rules' > 'Equal to', and configure your desired text color for specific outputs.

Full native support for modern XLOOKUP, IF, and IFS functions.100% format compatibility with Microsoft Excel (.xlsx) files.Intuitive Conditional Formatting interface for quick data highlighting.Free, lightweight, and fast-loading spreadsheet environment.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my IF formula not recognizing the TRUE value?

In spreadsheet formulas, TRUE and FALSE are special logical expressions. If you write "TRUE" with quotes, the application treats it as normal text. Always write TRUE without quotes when checking against logical values (e.g., A1=TRUE).

What does the "" at the end of the XLOOKUP formula do?

The "" (empty string) acts as the 'if_not_found' argument in the XLOOKUP function. Instead of returning a standard #N/A error when a match isn't found in the lookup array, the formula will elegantly return a blank cell.

Can I use VLOOKUP instead of XLOOKUP in this combination?

Yes, you can substitute XLOOKUP with VLOOKUP. However, you will need to nest it inside an IFERROR function to handle missing data gracefully, and adjust the column index number accordingly, which makes the formula slightly longer.

How do I edit the conditional formatting color later?

Go to the 'Home' tab, click 'Conditional Formatting', and choose 'Manage Rules'. Select 'This Worksheet' from the dropdown to locate your specific formatting rule, click 'Edit Rule', and select a new font or fill color.

Excel & Data Problems