Exceljet

Quick, clean, and to the point

Multiple columns are equal

Excel formula: Multiple columns are equal
Explanation 

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.

How this formula works

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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.