Explanation
In the example shown, we want to test if all values in each row are equal. To do this, we use an expression that compares the value in the first column (B5) to the rest of the columns (C5:F5):
B5=C5:F5
Because we are comparing one cell value to values in four other cells, the result is an array with four TRUE or FALSE values. In row 5, all values are equal, so all values are TRUE:
{TRUE,TRUE,TRUE,TRUE}
This array is returned directly to the AND function, which returns TRUE, since all values in the array are TRUE.
=AND({TRUE,TRUE,TRUE,TRUE}) // returns TRUE
In cell H6, B6=C6:F6 creates an array with two FALSE values, since D6 and F6 are different.
{TRUE,FALSE,TRUE,FALSE}
This array is delivered to the AND function, which returns FALSE:
=AND({TRUE,FALSE,TRUE,FALSE}) // returns FALSE
Counting differences
The formula in I5 uses the COUNTIF function to count differences in each row like this:
=COUNTIF(C5:F5,"<>"&B5)
The criteria is provided as “<>"&B5, which means “is not equal to B5”.
You can adjust the formula to mimic the behavior of the AND formula above like this:
=COUNTIF(C5:F5,"<>"&B5)=0
Here, we simply compare the result from COUNTIF to zero. A count of zero returns TRUE, and any other number returns FALSE.
This is not an array formula, so it does not require special handling.
Explanation
Normally, the MATCH function receives a single lookup value, and returns a single match if any. In this case, however, we are giving MATCH an array for lookup value, so it will return an array of results, one per element in the lookup array. MATCH is configured for “exact match”. If a match isn’t found, MATCH will return the #N/A error. After match runs, it returns have something like this:
=SUMPRODUCT(--(ISNA({3;5;6;2;#N/A;4})))>0
We take advantage of this by using the ISNA function to test for any #N/A errors.
After ISNA, we have:
=SUMPRODUCT(--({FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}))>0
We use the double negative (double unary) operator to convert TRUE FALSE values to ones and zeros, which gives us this:
=SUMPRODUCT({0;0;0;0;1;0})>0
SUMPRODUCT then sums the elements in the array, and the result is compared to zero for force a TRUE or FALSE result.
