Function Problems

How to Use IF with XLOOKUP to Return a Conditional Value in Excel

Phi Hung Vo Oct 21, 2026 868 views

Question details

The user wants to combine IF, IFERROR, and XLOOKUP to evaluate a lookup result, test conditions (such as finding a dash), and conditionally return an alternative value based on another cell.

How to Use IF with XLOOKUP for Conditional Values in Excel

Product: Excel
Device & OS: not provided
Scenario: Performing a complex multi-criteria lookup where the returned result needs to be evaluated. If the lookup returns a specific character like a dash, a secondary condition must be checked to determine the final output.
Observed behavior: The goal is to successfully return the primary XLOOKUP result, a conditional fallback value based on another cell's criteria, or a blank value if an error occurs, without causing circular reference issues.

Before you start

Make sure your version of Excel or WPS Office supports the XLOOKUP function, as it is only available in Microsoft 365, Excel 2021, and recent versions of WPS Spreadsheets.

Solution 1Recommended

Combine IF, IFERROR, and XLOOKUP into a Nested Formula

Use a nested formula structure to first attempt the XLOOKUP, check if it returns a specific character, apply a secondary IF condition, and wrap it all in IFERROR.

When combining these functions, XLOOKUP handles the primary data retrieval using multiple criteria. The IF function evaluates this result against your specific conditions, and IFERROR cleans up any unexpected errors, keeping your spreadsheet clean.

Select the target cell

Click on the cell where you want the final conditional value to be displayed.

Construct the primary XLOOKUP

Start by establishing your lookup logic. For a multi-criteria lookup, you multiply arrays to generate a 1 (TRUE) result. For example: XLOOKUP(1,(A15=A1:A50)*(B15=B1:B50),C1:C50,"-")

Wrap the lookup in an IF condition

Use the IF function to test if the XLOOKUP result equals a specific value (like a dash). If it does, test another cell (e.g., D1="lease") to provide an alternate result: =IF(XLOOKUP(...)="-", IF(D1="lease",E1,"-"), XLOOKUP(...))

Add IFERROR to handle exceptions

Wrap the entire formula in IFERROR to catch any unresolved calculation errors and output a blank cell instead. The final formula looks like this: =IFERROR(IF(XLOOKUP(1,(A15=A1:A50)*(B15=B1:B50),C1:C50,"-")="-",IF(D1="lease",E1,"-"),XLOOKUP(1,(A15=A1:A50)*(B15=B1:B50),C1:C50,"-")),"")

Verify your lookup ranges

Press Enter to apply the formula. Carefully check that your lookup arrays (e.g., A1:A50) do not unintentionally include your criteria cells (like A15 or B15). If they do, the formula might automatically return TRUE by evaluating itself. Adjust the range to A16:A50 if necessary.

Combine IF, IFERROR, and XLOOKUP into a Nested Formula

Array Range Consistency: Ensure all lookup arrays and return arrays are exactly the same size (e.g., rows 1 to 50). Mismatched array sizes will result in a #VALUE! error.

Advanced Data Analysis

Master Complex Formulas in WPS Spreadsheets

WPS Spreadsheets provides robust and native support for modern lookup functions including XLOOKUP, IF, and IFERROR. Easily execute multi-criteria lookups and manage conditional data processing with a familiar, fast, and highly compatible interface.

1. Open WPS Spreadsheets: Launch WPS Office and open your workbook in the Spreadsheets module.
2. Use the Formula Builder: Navigate to the Formulas tab and click 'Insert Function' to easily structure complex nested formulas without memorizing syntax.
3. Apply and Drag: Type your XLOOKUP and IF combination into the formula bar, press Enter, and use the fill handle to apply it across your data columns seamlessly.

Fully compatible with Microsoft Excel XLOOKUP and nested IF formulasFast processing engine for handling large datasets with complex array calculationsIntuitive formula auditing and error-checking toolsFree built-in templates to streamline your daily workflow

microsoft office alternative - wps office

Frequently Asked Questions

Why does my multi-criteria XLOOKUP return a circular reference warning?

This usually occurs if the cell containing your formula, or the specific criteria cell (e.g., A15), falls inside the lookup array range (e.g., A1:A50). Adjust your array ranges to exclude the criteria row to resolve this.

Do I have to press Ctrl+Shift+Enter for this formula to work?

No. In modern spreadsheet software (like Excel 365, Excel 2021, and latest WPS Spreadsheets) that support Dynamic Arrays and XLOOKUP, you only need to press Enter for array calculations to process correctly.

Can I use VLOOKUP instead of XLOOKUP for this specific task?

While possible, it requires significantly more complex workarounds. XLOOKUP is better suited because it natively handles multiple criteria by multiplying arrays (Array1=Criteria1)*(Array2=Criteria2), whereas VLOOKUP requires creating extra helper columns to combine criteria strings.

What is the purpose of the IFERROR function at the beginning?

The IFERROR function acts as a safety net. If the internal IF or XLOOKUP formulas generate an error like #N/A (value not found) or #VALUE!, IFERROR intercepts it and returns the value you specified at the end of the formula, such as an empty string (""), keeping your spreadsheet clean.

Excel & Data Problems