Excel array formula to check when all columns match, but ignore blanks
- Sean O'Shea
- May 7, 2022
- 1 min read
Tonight's tip of the night explains how you can use an array formula in Excel to confirm that values on a single row for multiple columns are the same, but discount any instances where a column has a blank entry. So if the same value to filled in one or more of the columns in the given range, and none of the other columns has a different value, or simply has a blank value, the result of the array formula will be TRUE.
In this example we have both numbers and text entered in columns B to E. The formula:
=(SUM(IFERROR(1/COUNTIF(B2:E2,B2:E2),0))=1)
. . . will check columns B to E on row 2. This is an array formula, so be sure to press CTRL + SHIFT when entering it to enclose it in curly brackets.
As you can see, regardless of whether numbers or text are use in a row range, the formula indicates if they match or differ, and skips over the cells with no content.