Formula Errors

How to Exclude Blank Results with XLOOKUP Alternatives in Excel

Kushani Nimanthika Sep 28, 2026 871 views

Question details

The user wants to create a dynamic, consolidated list that excludes blank entries using an alternative to XLOOKUP, while also resolving #REF! errors encountered during implementation.

How to Exclude Blank Results with XLOOKUP Alternatives in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Consolidating data into a cleaner, blank-free array using advanced Microsoft 365 formulas.
Observed behavior: Standard lookup formulas return blanks, and applying a nested dynamic array formula sometimes yields a #REF! error due to improper ranges or broken links.

Before you start

Verify that your version of Excel supports dynamic array functions (Microsoft 365 or Excel 2021+) and ensure your source data does not contain broken external worksheet links.

Solution 1Recommended

Use a LET-Based Dynamic Array Formula

Combine LET, FILTER, and TOCOL to create a consolidated list that automatically excludes blank values without needing a standard XLOOKUP.

While XLOOKUP is excellent for exact matches, it struggles to return dynamic lists that omit blanks. By leveraging the newer dynamic array functions available in Microsoft 365, you can filter and restructure data cleanly.

Identify your source data range

Determine the exact range of your dataset (e.g., A3:F20) and the criteria cell (e.g., J4) that will dictate what data to extract.

Apply the nested dynamic formula

Select your destination cell and enter the following formula in the Formula Bar: =LET(data,A3:F20,type,J4,frow,CHOOSEROWS(data,1),fltr,FILTER(data,frow=type),chcols,CHOOSECOLS(data,1),tocol,TOCOL(chcols,1),seq,SEQUENCE(ROWS(tocol)-1,,2),iffltr,IF(fltr=0,"",fltr),prod,CHOOSEROWS(tocol,seq),return,CHOOSEROWS(iffltr,seq),header,HSTACK("Product","Type"),result,HSTACK(prod,return),VSTACK(header,result))

Execute the formula

Press the Enter key. The formula will calculate and spill automatically into the neighboring cells to form a consolidated table without empty entries.

Automatic Updates: Any changes made to the source data range will automatically be reflected in this dynamically generated table.

Solution 2

Troubleshoot #REF! Errors in Your Array Formula

Fix #REF! reference errors that occur when adapting or copying the dynamic formula to a new worksheet.

Advanced Array Formulas in WPS

Manage Dynamic Arrays and Filter Blanks Effortlessly with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array functions like FILTER, TOCOL, and LET. You can seamlessly consolidate your datasets and exclude blanks without worrying about compatibility or complex errors.

1. Open Your Dataset: Launch WPS Spreadsheet and open the workbook containing the data you want to consolidate.
2. Select the Target Range: Click on the cell where you want the new consolidated, blank-free list to begin spilling.
3. Input the Dynamic Formula: Enter your LET or FILTER array formula into the Formula Bar and press Enter.
4. Verify the Results: WPS Spreadsheet will automatically calculate and spill the results into adjacent cells while cleanly ignoring blank values.

Fully compatible with Microsoft Excel formula syntax and file formats.Natively supports modern dynamic array functions including FILTER, TOCOL, and XLOOKUP.Lightweight and free alternative for managing complex spreadsheets.Built-in error checking tools to quickly identify and resolve #REF! or #VALUE! issues.

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting a #REF! error when using the FILTER and TOCOL functions?

A #REF! error typically occurs if the referenced cells were deleted, if the formula was copied to a new sheet without updating sheet references, or if the workbook contains broken external links to other files.

Can I use XLOOKUP to remove blank cells from a dataset?

XLOOKUP is designed to return a single matching record or row. While it has an 'if_not_found' argument for missing data, it cannot dynamically filter out multiple blank cells in an entire array. The FILTER and TOCOL functions are the correct alternatives for this task.

Will this LET and TOCOL formula work in older versions of Excel?

No, functions like LET, TOCOL, CHOOSEROWS, and HSTACK are only available in Microsoft 365 and Excel 2024. If you are using an older version, you will need to rely on complex INDEX/MATCH array formulas or VBA scripts.

Excel & Data Problems