Formula Errors

How to Remove FALSE Values from Excel Dynamic Array Formulas

John Wilson Oct 7, 2026 869 views

Question details

The user needs to extract matching employee names based on job title, status, and date without returning FALSE values, using dynamic arrays instead of VBA.

Remove FALSE Values from an Excel Dynamic Array Formula

Product: Excel
Device & OS: not provided
Scenario: Filtering employee data in a shared, multi-user workbook where macros and VBA cannot be used.
Observed behavior: The formula returns FALSE values alongside the matched employee names, or returns a #NAME? error if the function is misspelled or unsupported.

Before you start

Verify that your version of Excel supports dynamic array functions (such as Office 365 or Excel 2021 and later), as older versions will return a #NAME? error when attempting to use the FILTER or LET functions.

Solution 1Recommended

Use FILTER and LET Functions to Exclude FALSE Values

Wrap your existing evaluation logic inside the LET and FILTER functions to dynamically extract only the valid employee names and remove any FALSE returns.

When filtering data based on conditions like job title or date inside an IF statement, non-matching rows default to returning FALSE. By using the LET function, you can store this initial array as a variable, and then use the FILTER function to exclude the FALSE values from the final spilled array.

Select the target cell

Click on the top-left cell where you want the filtered list of employee names to appear. Ensure there is enough empty space below it for the array to spill.

Build the LET function

Type =LET(results, [Your_Current_IF_Formula], into the formula bar. This assigns your current formula (which generates the names and FALSE values) to a variable called 'results'.

Apply the FILTER function

Complete the formula by filtering the 'results' variable. Type FILTER(results, results<>FALSE, "No matches found")) and press Enter. The complete formula should look like: =LET(results, IF(A2:A100="Manager", B2:B100), FILTER(results, results<>FALSE, "No matches")).

Use FILTER and LET Functions to Exclude FALSE Values

Resolving #NAME? Errors: If the cell displays a #NAME? error, double-check your spelling for 'FILTER' and 'LET'. If spelled correctly, your Excel version does not support dynamic arrays.

Seamless Dynamic Arrays in WPS Spreadsheet

Filter Data Effectively with WPS Spreadsheet

WPS Spreadsheet fully supports modern dynamic array functions like FILTER and LET. You can easily remove FALSE values and extract clean lists of employee names without needing to write complex VBA scripts or worry about workbook compatibility.

1. Open your workbook in WPS: Launch WPS Spreadsheet and open the shared .xlsx workbook containing your employee data.
2. Enter the FILTER formula: Select the cell where you want the names to populate. Enter your formula using =FILTER(range, condition<>FALSE) to isolate the correct data.
3. Spill the array: Press Enter. WPS Spreadsheet will automatically spill the dynamic array down the column, showing only the matched names.

Full compatibility with Microsoft Excel (.xlsx) formats and dynamic array functions.Free, lightweight alternative with fast processing for large datasets.Ideal for shared workbooks, allowing multi-user collaboration without macro restrictions.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my dynamic array formula return FALSE instead of remaining blank?

This happens when an IF statement within your formula lacks the 'value_if_false' argument. If a condition isn't met (e.g., the job title doesn't match), the formula defaults to returning the boolean value FALSE.

Why am I getting a #NAME? error when trying to use FILTER?

A #NAME? error indicates that the software does not recognize the function name. This is usually caused by a typo in the formula (like typing 'FILTR' instead of 'FILTER') or using an older version of Excel that does not support dynamic array functions.

Why should I use the LET function alongside FILTER?

The LET function allows you to define a calculation once and reuse it within the same formula. Instead of making the software calculate your complex filtering logic twice (once to define the array and once to check if it equals FALSE), LET improves calculation performance.

Can I remove FALSE values without using dynamic arrays?

Yes, but it requires much more complex formulas combining INDEX, AGGREGATE, and MATCH, which must be manually dragged down the column. Dynamic arrays provide a much cleaner, automated solution that doesn't require VBA.

Excel & Data Problems