Excel’s AND function returns one value, either TRUE or FALSE; it does not return a separate result for each row in an array. To get a row-by-row array of results in Microsoft 365, combine comparison tests with multiplication and, if needed, compare the result with zero to produce TRUE/FALSE values.
What Excel AND Returns
AND tests whether every supplied logical condition is true. If all are true, it returns TRUE; if even one is false, it returns FALSE. It is useful when one overall decision depends on several conditions, such as checking that a person meets both an age requirement and a score requirement.
For example, with a person’s age in A2 and score in B2, the formula =AND(A2>=18,B2>=70) returns TRUE only when the age is at least 18 and the score is at least 70. This formula refers to one row, so the single result is exactly what you would expect.
The difference becomes important when a formula compares ranges. AND can examine multiple values, but it combines the tests into one overall answer. It does not preserve the individual results as an array. That distinction is the source of many confusing results when people expect AND to evaluate each row independently.
Why AND Does Not Return a Row-by-Row Array
Suppose you want to check whether each person’s value in column A is greater than 5 and the corresponding value in column B is less than 10. With values in A2:A4 and B2:B4, the formula =AND(A2:A4>5,B2:B4<10) returns one TRUE or FALSE, not three answers. Excel evaluates the conditions across the supplied ranges and reduces them to a single result: are all the tested values true?
This is different from a formula that produces an array containing one result per matching row. In a dynamic-array version of Excel, an array-producing formula can spill its results into the cells below or beside the formula. AND is a logical aggregation function, so its output remains a single value even when its inputs involve arrays.
A useful way to remember the distinction is that AND asks “Are all of these conditions true?” It does not ask “Is each row’s set of conditions true?” For the second question, create a row-wise calculation or use an array operation designed to retain one result per row.
Return TRUE or FALSE for Each Row
For a row-by-row result in Excel for Microsoft 365, multiply the two comparison arrays. Excel treats TRUE as 1 and FALSE as 0 in this arithmetic. With test values in A2:A4 and B2:B4, enter =((A2:A4>5)*(B2:B4<10))>0 in an empty cell. The formula returns a vertical array of TRUE/FALSE results, spilling into three cells if the spill area is clear.
For example, if A2:A4 contains 8, 3, and 9, while B2:B4 contains 7, 4, and 12, the first row passes both tests, the second fails the first test, and the third fails the second test. The result is TRUE, FALSE, FALSE. The final comparison with zero converts the 1/0 arithmetic back into explicit logical values.
If numeric results are useful instead, omit the final comparison. With the same layout, =(A2:A4>5)*(B2:B4<10) returns 1, 0, 0. A 1 means both conditions passed; a 0 means at least one failed. This form is common in calculations and as a filter criterion.
In Microsoft 365 for Windows, enter the formula in the first output cell and press Enter. The results spill automatically; do not select the full output range first or use Ctrl+Shift+Enter. Excel for Mac and Excel for the web also support dynamic arrays in current Microsoft 365 versions, though exact keyboard shortcuts differ by platform. Make sure cells needed for the spill are empty, or Excel may display a #SPILL! error.
Use AND Logic to Filter Rows
When the goal is to return matching records rather than a TRUE/FALSE list, use FILTER with multiplied conditions. FILTER is available in Microsoft 365 and Excel 2021 or later. For example, suppose A2:C5 contains employee names, region, and sales respectively, with names in A, region in B, and sales in C. To return rows where the region is West and sales exceed 100, use =FILTER(A2:C5,(B2:B5="West")*(C2:C5>100)).
Multiplying the tests implements AND logic: a row is included only when both comparisons are true. In the formula above, the region and sales conditions each produce an array of values, and their product identifies rows meeting both. FILTER then returns the corresponding full rows. If no rows qualify and no optional empty-result value is supplied, FILTER returns a #CALC! error.
With the same layout, to return only names for employees whose sales are above 100 and whose recorded units in column D are at least 5, the source range and criteria ranges must include the same rows. For a source in A2:D5, use =FILTER(A2:A5,(C2:C5>100)*(D2:D5>=5)). The result is a list of names, not a single overall TRUE/FALSE. Keep the dimensions aligned: each condition range should have the same number of rows as the array being filtered.
AND Versus Multiplication and Other Options
Multiplication is a compact way to express AND between matching array conditions, but it is not the same as calling the AND function. The product preserves a result for each corresponding position in the arrays. The AND function aggregates the logical inputs to one answer.
- Use AND when you want one answer for an entire record or a whole set of tests, such as checking two conditions for one row.
- Use multiplied comparisons when you need one result for each row or need a multi-condition include array for FILTER.
- Use addition between comparison arrays when you want OR logic: a row passes when at least one condition is true. If using the result as a logical array, compare it with zero.
- Use a helper column when the calculation should be visible, easy to audit, or compatible with older Excel workbooks.
For a helper column, put the row-level formula in the first data row and copy it down. With values in A2 and B2, enter =AND(A2>5,B2<10) in C2, then fill down. Each copied formula checks that row and returns one Boolean value. This is often clearer for shared spreadsheets than a single spilling formula.
Common Problems and How to Fix Them
A #SPILL! error usually means one or more cells where an array result should appear are occupied, merged, or otherwise blocking the output. Clear the cells in the spill path or move the formula. A formula that returns just one result may not be a spill problem at all: if it uses AND around range tests, the one result is expected.
Unexpected FALSE results often arise because AND requires every tested condition to pass. Check the individual comparisons separately, and verify that values are numbers or dates where expected. A number stored as text may not compare as intended; extra spaces in text can also prevent a text comparison from matching.
Errors in referenced cells can also affect the result. AND does not automatically ignore an error in a logical test; if a tested expression produces an error, the overall formula may show that error. Find the source cell or adjust the formula to handle the specific error condition rather than assuming AND will skip it.
Be careful with unequal range sizes in formulas that combine arrays. For example, a criterion range with three rows cannot correctly pair row by row with a source range of four rows. Align the starting and ending rows before using multiplication or FILTER. If the workbook uses an older Excel version without dynamic arrays, a range formula may require a different approach; a copied-down helper column is generally straightforward and reliable.
When to Choose a Helper Column or Dynamic Array
A dynamic-array formula is convenient when you want a compact, automatically expanding result and your Excel version supports the functions and behavior used. If new records are added to the source, using an Excel Table can make the data easier to manage; structured references can expand with the table when formulas are set up to use them.
A helper column is a good choice when others need to inspect or filter each row’s logical result, when the workbook must work in older Excel versions, or when troubleshooting matters more than formula compactness. It also makes it easy to count passing rows or apply conditional formatting to a visible TRUE/FALSE field.
For a one-off check across several conditions in a single record, ordinary AND is still the clearest tool. Choose based on the shape of the required output: one overall answer calls for AND; a list of row-level answers calls for array arithmetic or a row formula; matching records call for FILTER with combined criteria.
Frequently Asked Questions
Can Excel AND return multiple TRUE or FALSE values?
No. AND returns one result. For separate results by row in Microsoft 365, use multiplied comparisons such as =((A2:A4>5)*(B2:B4<10))>0, or calculate AND in a helper column and copy it down.
Why does AND with a range return only one answer?
AND is designed to combine logical tests into a single answer. When its arguments refer to ranges or array calculations, it still reports whether all the evaluated conditions are true overall; it does not return each row’s result independently.
How do I use AND with FILTER?
Multiply the condition arrays inside FILTER. For instance, with values in B2:B5 and C2:C5, =FILTER(A2:A5,(B2:B5>100)*(C2:C5>=5)) returns the entries in A2:A5 where both conditions pass. FILTER requires Microsoft 365 or Excel 2021 and later.
Can I get a 1 or 0 instead of TRUE or FALSE?
Yes. With values in A2:A4 and B2:B4, =(A2:A4>5)*(B2:B4<10) returns 1 where both conditions are true and 0 otherwise. Add >0 at the end if you specifically want logical TRUE/FALSE values.
Does the same approach work for OR conditions?
Yes. Add comparison arrays rather than multiplying them. With values in A2:A4 and B2:B4, =((A2:A4>5)+(B2:B4<10))>0 returns TRUE for a row when either condition is true, and also when both are true.
Related
- table() in RStudio: How It Counts and Displays Data
- Data Validation and Reconciliation in Excel Explained
- PowerPoint Table Diagonal Split Cell: Practical Methods
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula Greyed Out? Causes and FixesIf Excel’s formula commands are greyed out, first press Esc to leave cell-edit mode, then select a worksheet…
- Freeze Function in Excel: Freeze Panes or FormulasTo keep headings visible while you scroll, select the cell just below and to the right of the…
- Excel INDEX and AVERAGE: How to Combine ThemExcel has no single function called INDEX AVERAGE; instead, combine INDEX with AVERAGE so Excel averages the value…
- Excel TEXT Function Format Options and Examples ExplainedExcel’s TEXT function converts a number, date, or time into text displayed with a format code: use =TEXT(value,…
- SUMIF in Excel: Step-by-Step Formula, Examples, and TipsTo total values that match one condition in Excel, use SUMIF. If categories are in A2:A7 and amounts…
- Excel Date Difference in Years: Formulas That WorkTo find the number of complete years between two dates in Excel, use =DATEDIF(A2,B2,"Y"), with the earlier date…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.