Formula Errors

How to Use XLOOKUP When an IFS Formula Returns No Match in Excel

WPS Content Manager Oct 1, 2026 868 views

Question details

The user needs a formula to look up a section and zone on another worksheet if none of the primary IFS conditions (such as VIP, OUTSIDE, or BALCONY) are met.

How to Use XLOOKUP When an IFS Formula Returns No Match in Excel

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Assigning special zones based on specific text conditions, and requiring a fallback lookup on a different sheet when no exact string match is found by the initial formula.
Observed behavior: The standard IFS function returns an error when none of its conditions are evaluated as TRUE, instead of triggering a secondary lookup process.

Before you start

Ensure that your fallback lookup table on the other worksheet is formatted correctly and that you have identified the exact cell ranges (or defined named ranges) for your XLOOKUP function to reference.

Solution 1Recommended

Wrap the IFS Formula with IFERROR and XLOOKUP

Use the IFERROR function to catch the error generated when IFS finds no match, and execute an XLOOKUP function to retrieve the zone from another worksheet.

The most robust way to handle a failing IFS statement is by utilizing IFERROR. By nesting your primary IFS logic inside IFERROR, you can instruct the spreadsheet to run a completely different formula—in this case, XLOOKUP—whenever the IFS conditions fall through.

Select the target cell

Click on the cell where you want the final zone assignment to be displayed.

Start the IFERROR function

Begin your formula by typing `=IFERROR(` to handle cases where the primary conditions are not met.

Insert the primary IFS logic

Enter your IFS function as the first argument. For example: `IFS(ISNUMBER(SEARCH("VIP",A1)),"ZONE1",ISNUMBER(SEARCH("OUTSIDE",A1)),"ZONE5",ISNUMBER(SEARCH("BALCONY",A1)),"ZONE5")`.

Add the XLOOKUP fallback

Type a comma, then insert your fallback lookup logic as the second argument: `XLOOKUP(A1, Sections, Zones, "Not found")`.

Apply the combined formula

Close the final parenthesis and press Enter. The complete formula will look like: `=IFERROR(IFS(ISNUMBER(SEARCH("VIP",A1)),"ZONE1",ISNUMBER(SEARCH("OUTSIDE",A1)),"ZONE5"), XLOOKUP(A1, Sections, Zones, "Not found"))`.

Wrap the IFS Formula with IFERROR and XLOOKUP

Named Ranges Reminder: If you have not defined 'Sections' and 'Zones' as named ranges in your workbook, replace those terms in the XLOOKUP function with your actual cell references (e.g., Sheet2!$A$2:$A$100, Sheet2!$B$2:$B$100).

Solution 2

Use TRUE as the Final IFS Condition for Fallback

Instead of relying on an error-catching function, you can set the final condition of your IFS statement to TRUE, which acts as a default trigger for the XLOOKUP.

Advanced Formula Management

Easily Manage Nested Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports advanced logical and lookup functions like IFS, IFERROR, and XLOOKUP, allowing you to seamlessly execute complex conditional formatting and data retrieval without errors.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open the file containing your section and zone data.
2. Type your nested formula: Select the target cell and type your combined `=IFERROR(IFS(...), XLOOKUP(...))` formula.
3. Select ranges visually: Use your mouse to easily highlight the cross-sheet arrays for your XLOOKUP arguments; WPS will automatically format the syntax.
4. Apply and drag: Press Enter to generate the result, then drag the fill handle down to apply the fallback formula to the rest of your column.

Fully compatible with Microsoft Excel formulas, functions, and file formats (.xlsx).Provides real-time formula syntax tooltips to help you write complex nested IFERROR and XLOOKUP combinations effortlessly.Lightweight, fast, and completely free to use for your daily data analysis and zone mapping tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my IFS formula return an #N/A error when there is no match?

The IFS function evaluates conditions in order. If none of the provided conditions evaluate to TRUE, it has no result to return and throws an #N/A error. To prevent this, you must either provide a default TRUE condition at the end or wrap the formula in IFERROR.

Can I use VLOOKUP instead of XLOOKUP as the fallback?

Yes, you can substitute XLOOKUP with VLOOKUP in the IFERROR wrapper. However, XLOOKUP is generally preferred because it has a built-in 'if not found' argument and doesn't require you to manually count column index numbers.

How do I reference a lookup table on another worksheet?

When typing your XLOOKUP function, simply click on the tab of the other worksheet and highlight the lookup array, type a comma, and highlight the return array. Your formula will automatically populate with sheet references, looking something like `Sheet2!A:A`.

Excel & Data Problems