Function Problems

How to Return Values from Column A When Column B is TRUE in Excel

Kushani Nimanthika Sep 25, 2026 869 views

Question details

The user needs to filter one column to return only the corresponding values where an adjacent column contains the value TRUE.

How to Return Values from Column A When Column B is TRUE in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Filtering source data that is part of an array and cannot be manually sorted using traditional sorting tools.
Observed behavior: The user wants to generate a continuous output of matched values from Column A based on the TRUE condition in Column B, without any blank entries appearing between the results.

Before you start

Ensure you are using an Office version that supports Dynamic Arrays (such as Microsoft 365 or Office 2021), as older versions do not support the FILTER function.

Solution 1Recommended

Use the FILTER Function to Extract Corresponding Values

The dynamic array FILTER function allows you to quickly extract data based on boolean conditions without needing to manually sort or remove blank rows.

The FILTER function is designed to return an array of values that meet one or more criteria. Since you want to check for TRUE conditions in Column B, the formula evaluates the entire range and spills the matching Column A items into adjacent cells automatically.

Select the Output Cell

Click on an empty cell where you want the top of your new filtered list to appear. Make sure there is enough empty space below it for the results to spill into.

Enter the FILTER Formula

Type the formula =FILTER(A1:A5, B1:B5=TRUE) into the formula bar. Replace A1:A5 with your actual data range in Column A, and B1:B5 with your condition range in Column B.

Execute the Formula

Press Enter. The cell will display the first matched result, and all other matching values will automatically populate in the cells directly below it.

Use the FILTER Function to Extract Corresponding Values

Dynamic Updates: Because FILTER is a dynamic array formula, if any value in Column B changes to TRUE or FALSE later, your filtered list will update instantly.

WPS Spreadsheet Solutions

Filter Array Data Effortlessly in WPS Spreadsheets

WPS Office fully supports dynamic array formulas, allowing you to use the FILTER function seamlessly. It is an excellent choice for efficiently organizing large datasets without altering your original arrays.

1. Open your Document in WPS: Launch WPS Spreadsheets and open the workbook containing your array data.
2. Select the Destination Cell: Click on the blank cell where you wish to display your filtered results.
3. Input the Formula: Type =FILTER(A1:A5, B1:B5=TRUE) and press Enter to instantly extract the values.

Fully compatible with Microsoft Excel (.xlsx) file formats and formulas.Supports modern dynamic array functions including FILTER, SORT, and UNIQUE.Lightweight, fast, and completely free for standard daily office tasks.Familiar user interface requiring zero learning curve for Excel users.

microsoft office alternative - wps office

Frequently Asked Questions

What if my version of Excel does not support the FILTER function?

If you are on an older version of Excel (like 2016 or 2019), you cannot use dynamic array formulas. You will need to use a combination of INDEX, SMALL, and ROW functions, or simply use the Data > Filter tool on the Ribbon to hide rows where Column B is not TRUE.

How can I prevent the #CALC! error if there are no TRUE values?

The #CALC! error appears when the FILTER function finds empty results. You can fix this by adding the optional 'if_empty' argument. Change your formula to =FILTER(A1:A5, B1:B5=TRUE, "No matches").

Can I filter based on text instead of TRUE or FALSE?

Yes. If Column B contains specific text (e.g., 'Yes' or 'Approved'), simply update the condition in the formula. For example, use =FILTER(A1:A5, B1:B5="Approved").

Excel & Data Problems