Function Problems

How to Find the First Nonblank Cell in an Excel Range Using XLOOKUP

Emma Brown Oct 9, 2026 869 views

Question details

The user wants to retrieve the first nonblank value from a single-column range using the XLOOKUP function, accounting for both truly empty cells and cells containing null strings.

How to Find the First Nonblank Cell in an Excel Range Using XLOOKUP

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Working with a column of data where some cells are empty or contain formulas outputting empty strings, and needing to extract the first actual data entry.
Observed behavior: Standard lookups may fail or return incorrect results because cells filled with null values or empty strings from formulas are not treated as truly empty by default functions.

Before you start

Verify whether the 'blank' cells in your target range are entirely devoid of data or if they contain formulas that return invisible null strings (""), as this will determine which formula to use.

Solution 1Recommended

Use XLOOKUP with ISBLANK for Truly Empty Cells

This method is the standard approach when the empty cells in your range contain no data, spaces, or formulas whatsoever.

The ISBLANK function checks a cell to see if it is completely empty. By nesting this inside XLOOKUP and searching for a FALSE result, we can pinpoint the first cell that actually contains data.

Select your result cell

Click on the cell where you want the first nonblank value to be displayed.

Enter the XLOOKUP formula

Type the formula =XLOOKUP(FALSE, ISBLANK(Your_Array), Your_Array) into the formula bar. Replace 'Your_Array' with your actual data range, for example, A2:A20.

Apply the formula

Press Enter. The function evaluates the range for non-blank cells (where ISBLANK is FALSE) and returns the first matching value.

Use XLOOKUP with ISBLANK for Truly Empty Cells

Data Condition: If this formula returns a seemingly blank result, your cells likely contain invisible null strings rather than being truly empty.

Solution 2

Use XLOOKUP with ISNUMBER for Cells with Null Values

Use this approach when your cells appear blank but actually contain empty strings or null values generated by other formulas.

Advanced Spreadsheet Functions

Use XLOOKUP Flawlessly in WPS Spreadsheet

WPS Spreadsheet fully supports advanced array functions like XLOOKUP, ISBLANK, and ISNUMBER. It allows you to handle complex data lookups seamlessly, providing a powerful and highly compatible environment for all your data analysis tasks.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office, open Spreadsheet, and load the document containing your data.
2. Select the output cell: Click on the specific cell where you wish to display the first non-blank value.
3. Input the lookup formula: Type =XLOOKUP(FALSE, ISBLANK(A2:A20), A2:A20) and press Enter to instantly extract the data.

Fully compatible with Microsoft Excel formats (.xlsx) and formulasNative support for advanced dynamic arrays and XLOOKUPLightweight architecture for fast processing of large datasetsFree and intuitive user interface with a familiar layout

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't ISBLANK work if the cell has a formula returning an empty string?

The ISBLANK function is designed to evaluate to TRUE only if a cell is completely devoid of any content. If a cell contains a formula, even if that formula evaluates to an empty string ("") or null value, the cell technically contains data (the formula itself), so ISBLANK returns FALSE.

Can I use XLOOKUP to find the first non-blank text value instead of a number?

Yes. If your range contains null strings and you want to locate the first actual text string, you can evaluate the length of the cell contents instead. Use the formula =XLOOKUP(TRUE, LEN(Your_Array)>0, Your_Array) to find the first cell that has a character length greater than zero.

Is the XLOOKUP function available in older versions of Excel?

No, XLOOKUP is only available in Microsoft 365, Excel 2021, and modern alternatives like WPS Office. For older spreadsheet versions, you must use a combination of INDEX and MATCH paired with array formulas to achieve the same result.

Excel & Data Problems