Function Problems

How to Combine XLOOKUP Formulas with IF or OR in WPS Office Spreadsheets

Muhammad Talha Nov 13, 2026 869 views

Question details

The user wants to condense two XLOOKUP formulas that return different results based on whether the lookup result is blank into one shorter formula using IF, OR, or LET in WPS Office Spreadsheets.

How to Combine XLOOKUP Formulas with IF or OR in WPS Office Spreadsheets

Product: WPS Office Spreadsheets
Device & OS: not provided
Scenario: The user is writing a lookup formula that references a DATABASE sheet and needs a conditional result based on whether the returned cell in column AC is blank.
Observed behavior: Two XLOOKUP formulas currently repeat the lookup and return different results depending on a blank lookup result; the user wants a shorter combined formula.

Before you start

Open your WPS Office Spreadsheets workbook and confirm that the lookup sheet is named DATABASE and that D7 contains the lookup value. Also check that XLOOKUP and LET are available by typing =XLOOKUP( in a blank cell and watching for the function name.

Solution 1Recommended

Use LET to Reuse One XLOOKUP Result and Apply an IF Condition

Best when you want the shortest formula and do not want to run the same XLOOKUP twice.

The LET function stores the XLOOKUP result in a variable and lets the IF condition reference that variable, which makes the formula shorter and easier to maintain.

Select the formula cell

Click the cell where you want the combined result, for example E7.

Enter the LET formula

In the formula bar, type =LET(result,XLOOKUP(D7,DATABASE!D:D,DATABASE!AC:AC),IF(result="","Awaits Training",result)) and press Enter.

Verify the returned value

Check that the cell shows "Awaits Training" when column AC is blank and otherwise shows the value returned by XLOOKUP.

Use LET to Reuse One XLOOKUP Result and Apply an IF Condition

Shortened formula works: The XLOOKUP runs once, so the formula is shorter and easier to maintain.

Solution 2

Use IF with XLOOKUP to Return a Fixed Text for Nonblank Results

Use when every nonblank lookup result should return the same text, such as Not required.

Solution 3

Use OR with XLOOKUP to Test Multiple Conditions

Use when blank and another text value should both produce the same result.

WPS Spreadsheets formula solution

Shorten XLOOKUP IF and OR Formulas in WPS Office Spreadsheets

WPS Office Spreadsheets supports XLOOKUP and LET, so you can perform a lookup once and wrap it with IF or OR directly in the formula bar. This creates shorter, easier-to-audit formulas while keeping full compatibility with Microsoft Excel .xlsx workbooks.

1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office, open the .xlsx workbook that contains the DATABASE sheet, and select the cell where the formula result should appear.
2. Enter the combined LET formula: In the formula bar, type =LET(result,XLOOKUP(D7,DATABASE!D:D,DATABASE!AC:AC),IF(result="","Awaits Training",result)) and press Enter.
3. Adjust the condition for your result: To return "Not required" for every nonblank result, replace the IF formula with =IF(XLOOKUP(D7,DATABASE!D:D,DATABASE!AC:AC)="","Awaits Training","Not required"), or wrap XLOOKUP in OR when testing multiple values.
4. Save the Excel-compatible file: Press Ctrl+S to save the workbook. WPS Office keeps the file in .xlsx format, so it remains compatible with Microsoft Excel.

Use XLOOKUP and LET to shorten repeated lookup formulasReturn conditional values such as Awaits Training or Not requiredOpens, edits, and saves Microsoft Excel .xlsx files with formula compatibilityFree WPS Office Spreadsheets with a familiar formula bar and function libraryLightweight Office alternative for everyday spreadsheet tasks

microsoft office alternative - wps office

Frequently Asked Questions

Can I use XLOOKUP in WPS Office Spreadsheets?

Yes. Current WPS Office Spreadsheets builds support XLOOKUP. Type =XLOOKUP( in the formula bar, or use the Formulas tab and choose Lookup & Reference to insert it.

How do I return Awaits Training only when XLOOKUP returns a blank?

Use the LET version: =LET(result,XLOOKUP(D7,DATABASE!D:D,DATABASE!AC:AC),IF(result="","Awaits Training",result)). This keeps the actual nonblank result and returns the text when the result is empty.

Can I combine XLOOKUP with OR in WPS Spreadsheets?

Yes. Use =IF(OR(XLOOKUP(D7,DATABASE!D:D,DATABASE!AC:AC)="",XLOOKUP(D7,DATABASE!D:D,DATABASE!AC:AC)="Awaits training"),"Awaits training","Not required"). However, this repeats the lookup, so consider LET for a shorter formula.

Why should I use LET with XLOOKUP?

LET stores the XLOOKUP result in a variable, so you only perform the lookup once. The formula is shorter, easier to edit, and recalculates faster than repeating XLOOKUP inside IF or OR.

What can I do if my WPS Office version does not have XLOOKUP or LET?

Update WPS Office to the current version from the official WPS website. In older versions, use INDEX and MATCH inside IF as an alternative.

Excel & Data Problems