How to Return Only Nonblank Values Using XLOOKUP in Excel
Question details
The user wants to retrieve only nonblank values for a specific lookup criteria using XLOOKUP.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Extracting employee training records where only completed or assigned operations should be displayed, skipping any blank cells in the dataset.
- Observed behavior
- Standard XLOOKUP returns all matching columns in the return array, outputting 0 or a blank for empty cells instead of excluding them entirely from the final results.
Ensure your spreadsheet software supports dynamic array functions, specifically XLOOKUP and FILTER, as older versions of Excel do not support these array behaviors.
Combine FILTER and XLOOKUP to Exclude Blanks
Use the FILTER function to wrap your XLOOKUP result, evaluating the array to remove any blank or zero values dynamically.
By default, XLOOKUP will return the entire corresponding row or column, including empty cells. Wrapping the XLOOKUP function in a FILTER function allows you to specify a nonblank condition, extracting only the relevant training operations.
Click on the empty cell where you want the filtered dynamic array to begin spilling.
Type the XLOOKUP formula to retrieve the employee's training values. For example: XLOOKUP(EmployeeName, Names, TrainingValues).
Enclose your XLOOKUP formula inside the FILTER function and set the condition to exclude blanks. The final formula should look like: =FILTER(XLOOKUP(EmployeeName,Names,TrainingValues), XLOOKUP(EmployeeName,Names,TrainingValues)<>"").
Press Enter on your keyboard. The array will dynamically spill, displaying only the nonblank training operations for that employee.
Return Operation Headers and Values as Pairs
Filter both the operation headers and the employee's corresponding values together to display a matched pair of operation names and training levels.
Master XLOOKUP and Dynamic Arrays with WPS Office
WPS Spreadsheet natively supports advanced functions like XLOOKUP, FILTER, and dynamic arrays. You can easily build powerful, automated data extraction models to handle complex lookup tasks without extensive scripting.
- 1. Open WPS Spreadsheet: Download and launch WPS Office, then open your dataset in WPS Spreadsheet.
- 2. Select your output cell: Click on an empty cell where you want to output the filtered training data.
- 3. Enter the formula: Type the formula combining FILTER and XLOOKUP, exactly as you would in Microsoft Excel.
- 4. View your results: Press Enter to spill the nonblank training operations automatically into the adjacent cells.
Frequently Asked Questions
Why does my XLOOKUP return a 0 instead of leaving the cell blank?
By default, spreadsheet applications evaluate blank source cells in the return array as zeros. To avoid this, you can append &"" to the end of your formula or use the FILTER function to exclude them entirely.
Can I return multiple columns with XLOOKUP?
Yes, XLOOKUP is designed to return an array of values. If your return array spans multiple columns or rows, the function will spill the results across adjacent cells dynamically, assuming there is empty space.
What if the lookup criteria is not found?
You can use the built-in [if_not_found] argument in the XLOOKUP function. For example, formatting your formula as XLOOKUP(A2, Names, Values, "Not Found") will return the text "Not Found" instead of displaying an #N/A error.
Why is my FILTER formula returning a #CALC! error?
The #CALC! error occurs in the FILTER function when the condition results in an empty array (meaning there are zero nonblank values to return). You can prevent this error by adding the optional [if_empty] argument, such as FILTER(array, include, "No Training Records").
