Function Problems

How to Return Blank for 0% or 100% Using XLOOKUP in Excel

Maira Mehtab Sep 22, 2026 870 views

Question details

The user needs an Excel XLOOKUP formula to return the word 'Blank' when the lookup value is 0% or 100%, while returning the actual percentage for all other matches.

Product: Excel
Device & OS: not provided
Scenario: Looking up percentage values from another dataset across multiple spreadsheets where extreme boundary values (0% or 100%) need to be masked or labeled as 'Blank'.
Observed behavior: By default, XLOOKUP returns the exact matched value. The goal is to evaluate the resulting value and conditionally replace the output with 'Blank' if it equals 0 or 1.

Before you start

Ensure that the source data is properly formatted as numerical percentages (where 100% equals 1 and 0% equals 0) rather than text strings, to allow accurate logical comparisons in your formula.

Solution 1Recommended

Use LET, IF, and OR Functions in Combination with XLOOKUP

Combine XLOOKUP with the LET function to store the lookup result as a variable, then use IF and OR to evaluate if it equals 0% or 100%.

The LET function allows you to calculate the XLOOKUP result once and assign it a name (like 'x'). This makes the formula more efficient and prevents you from having to type the long XLOOKUP formula multiple times.

By wrapping the entire logic in an IFERROR function, you also ensure that if the lookup fails entirely, the cell will gracefully display 'Blank'.

Start the LET function

Type `=IFERROR(LET(x, ...)` to declare a variable 'x' that will hold your lookup result and handle any initial errors.

Define the XLOOKUP logic

Insert your standard XLOOKUP formula as the variable definition. For example: `XLOOKUP($A$1, '[2025 Team Stats.xlsx]January'!$A$1:$A$500, '[2025 Team Stats.xlsx]January'!$B$1:$B$500)`.

Add the conditional logic

Add the IF and OR statement to evaluate 'x': `IF(OR(x=100%, x=0%), "Blank", x)`. This checks if the result is exactly 1 or 0.

Complete the formula

Close the formula with the IFERROR fallback: `), "Blank")`. The final formula is: `=IFERROR(LET(x, XLOOKUP($A$1, '[2025 Team Stats.xlsx]January'!$A$1:$A$500, '[2025 Team Stats.xlsx]January'!$B$1:$B$500), IF(OR(x=100%,x=0%), "Blank", x)), "Blank")`.

Percentage Values in Formulas: In Excel formulas, 100% is mathematically equivalent to the number 1, and 0% is equivalent to 0. You can safely use either 100% or 1 in your OR statement.

Advanced Formula Support

Master Advanced Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports advanced Excel functions like XLOOKUP, LET, and IFERROR, allowing you to manipulate cross-workbook data seamlessly.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook using WPS Spreadsheet.
2. Verify Data Formats: Select your source data and ensure it is formatted correctly using the 'Format Cells' option.
3. Apply Formula: Paste the `=IFERROR(LET(...))` formula into your desired cell and press Enter to dynamically filter your percentages.

100% compatibility with Microsoft Excel formulas and functionsCross-file data referencing works perfectly and intuitivelyRich formula auto-completion for complex logic buildingFree, lightweight, and fast performance for large datasets

microsoft office alternative - wps office

Frequently Asked Questions

Why does my formula return 'Blank' for values that look like 99.6%?

If you have cell formatting set to hide decimals, a value like 99.6% might visually display as 100%. The underlying mathematical value is not exactly 1, so the OR logic will not trigger. Use the ROUND function if you need to evaluate the rounded value instead of the exact one.

Can I do this without the LET function?

Yes, but you will have to write the XLOOKUP formula twice. For example: `=IF(OR(XLOOKUP(...) = 1, XLOOKUP(...) = 0), "Blank", XLOOKUP(...))`. This makes the formula longer, harder to read, and slower to calculate.

What if my XLOOKUP doesn't find a match at all?

The IFERROR wrapper at the beginning of the formula `=IFERROR(..., "Blank")` ensures that if XLOOKUP returns an #N/A error due to a missing match, the cell will safely display 'Blank' instead of an error code.

Excel & Data Problems