Summary

To test if values in multiple columns are the same, you can use a simple array formula based on the AND function. In the example shown, the formula in H5 is:

{=AND(B5=C5:F5)}

Note: this is an array formula and must be entered with control + shift + enter, unless you are using Excel 365, where array formulas are native.

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.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.