I am trying to use the AND function with structured references and keep getting errors. I have tried everything in the help notes and I continue to get the same errors.

Question

I am trying to use the AND function with structured references and keep getting errors. I have tried everything in the help notes and I continue to get the same errors.

Answer 1

Hi @Coffman, Brandi [HHS],

For further assistance, please share more details about your situation like which error did you have when using AND formula and screenshots of your sample data worksheet or your expected results so I can clearly understand your situation and provide the most appropriate formula/guidance for your concern.

Please understand that my initial response may not always resolve the issue immediately. However, with your help and more detailed information, we can work together to find a solution.

Thank you for your understanding and cooperation. I look forward to hearing from you.

---

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread

Answer 2

The AND function requires each argument to evaluate to a logical value (TRUE/FALSE) or to a range/column that contains logical values. With structured references in Excel tables, errors usually come from one of these issues:

1. Using non-logical columns directly
   • AND expects logical values or expressions that return TRUE/FALSE.
   • If a structured reference points to a column with text or numbers (not logical values), AND will either ignore them (in some products) or return an error if no logical values are present.
   • Fix: Wrap structured references in comparisons so they evaluate to TRUE/FALSE, for example:

          =AND(Table1[Amount] > 0, Table1[Status] = "Open")
     

2. Entire-column structured references without logical content
   • If the referenced column has no logical values at all, AND returns #VALUE!.
   • Fix: Ensure the column contains TRUE/FALSE values, or convert it with comparisons:

          =AND(Table1[FlagColumn])              // if FlagColumn is TRUE/FALSE
          =AND(Table1[Score] >= 50)             // converts numbers to TRUE/FALSE
     

3. Mixed or invalid data types in the referenced column
   • Text or empty cells in an array/range argument are ignored by AND in Excel. If all cells are text/empty (no logicals), AND returns #VALUE!.
   • Fix: Clean the column or use explicit logical tests:

          =AND(Table1[Date] >= DATE(2024,1,1))
     

4. Incorrect syntax with structured references
   • Each condition must be a separate argument, separated by commas:

          =AND(Table1[Amount] > 0, Table1[Amount] < 1000)
     

   • Do not chain comparisons like Table1[Amount] > 0 < 1000; Excel does not support chained comparisons. Use AND to combine them, as in:

          =AND(Table1[Amount] > 0, Table1[Amount] < 1000)
     

If the formula still errors, check:

• That each structured reference points to an existing table/column name.
• That parentheses and commas are correctly placed (no syntactic errors).
• That each argument to AND ultimately evaluates to TRUE/FALSE or to a column of logical values.

---

References:

• AND function
• Create Formulas for Calculations in Power Pivot
• AND ER function
• SUMIF returning #N/A-Function Argument returns FALSE when TRUE ??? - Microsoft Q&A