Excel: Check if a set of rows is equal to another set

Question

Is there a command in Excel to check if a set of rows is directly equal to another set. For instance I want to check if the values inside rows 1 to 10 is equal to another set of rows 13 to 22.

That is the values inside row 1 corresponds to row 13, row 2 corresponds to row 14, row 3 corresponds to row 15 and so on.

I know how to compare a row with another row using COUNTIFS(1:1, 13:13) but how to compare a range of rows using a single function?

CallumDA · Accepted Answer · 2022-04-15T10:57:02.243

3

This will return True if all values are identical, False otherwise.

=AND(A1:Z10=A13:Z22)

This is an array formula so you must enter it using Ctrl+Shift+Enter rather than just Enter. You'll know you've done it right when Excel puts curly braces {} around the formula in the formula bar.

In later versions of Excel (e.g. 365) you won't need to enter this as an array formula

edited Apr 15 '22 at 10:57

answered Jun 08 '17 at 07:41

CallumDA

12,025
6
30
52

1

Beautiful solution – a-burge Jun 08 '17 at 07:45
Thank you, what if the cell values has text too, instead of just numbers? – jackuars Jun 08 '17 at 08:15
@jackuars, that would work as well. The formula just checks that values are the same, it isn't concerned with the type of data – CallumDA Jun 08 '17 at 08:17

Excel: Check if a set of rows is equal to another set

1 Answers1