Fix Excel AND Formula Returning FALSE in a Structured Table
Question details
The user needs to correct an AND function inside a structured Excel table that evaluates to FALSE even when all text conditions appear to be met.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Using an AND formula in a structured Excel table to verify if multiple text criteria are true simultaneously, such as checking if Project Type is "Drama" and Approved is "No".
- Observed behavior
- The formula incorrectly returns FALSE despite the visible data in the row seemingly satisfying all the specified conditions.
Verify that your data range is formatted as an official Excel Table and double-check that the column header names in your table perfectly match the names referenced in your formula.
Use Correct Structured References for the Current Row
Excel tables use a specific syntax called structured references to refer to data. Using incorrect syntax instead of row-level identifiers will lead to evaluation errors.
When writing formulas inside an official Excel Table, it is best practice to use the [@ColumnName] syntax. The '@' symbol specifically tells Excel to look at the data in the same row as the formula, rather than evaluating the entire column.
Click the cell in your table's formula column where you want to evaluate the conditions.
Type =AND( into the formula bar to begin.
Type the reference for the current row's column, followed by your criteria. For example: [@[Project Type]]="Drama".
Insert a comma, and add your next condition using the same syntax: [@[Approved?]]="No".
Close the parentheses so your final formula is =AND([@[Project Type]]="Drama",[@[Approved?]]="No") and press Enter.
Clean Source Data to Remove Hidden Characters
If the formula syntax is perfect but still returns FALSE, the source cells likely contain leading spaces, trailing spaces, or non-breaking spaces that make exact text matches fail.
Easily Manage Table Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports advanced table formatting, structured references, and logical functions like AND, OR, and IF. Its intuitive interface makes it easy to troubleshoot and build complex table formulas without hassle.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your data table.
- 2. Use point-and-click for references: Type =AND( into your target cell, then simply click on the cells in the same row. WPS will automatically generate the correct [@ColumnName] structured references for you.
- 3. Apply across the table: Press Enter, and WPS Spreadsheet will instantly calculate and populate the formula for the entire column.
Frequently Asked Questions
Why does my AND formula work in normal cells but fail in an official table?
Normal cells use standard grid references (like A2 or B2). When you convert a range to an official Table, the program expects structured references (like [@ColumnName]) to evaluate the current row correctly. If you try to mix them or accidentally select the whole column instead of the row, the logical test may fail.
How do I quickly check if invisible characters are causing my formula to return FALSE?
You can use the LEN function (e.g., =LEN(A2)) to count the exact number of characters in a cell. If the visible word is "No" (which is 2 characters) but the LEN formula returns 3 or more, there are hidden spaces. You should use the TRIM function to remove them.
Can I use the AND function with numbers instead of text in a structured table?
Yes, absolutely. For numeric evaluations, simply remove the quotation marks around your criteria in the formula. For example, to check if Revenue is over 1000 and Cost is under 500, you would write: =AND([@[Revenue]]>1000, [@[Cost]]<500).
