How to check if two ranges are equal in Excel?
In Excel, we may run into the situation that we need to check whether two ranges are equal cell-by-cell and return the logical value TRUE or result YES when all the cells in the corresponding positions match. In this tutorial, we will talk about two quick ways to compare two ranges.
In this case, we have two ranges of data in the table below. It is hard to determine whether the two ranges are equal by checking the multiple corresponding cells one by one. With the formulas below, things will be much easier. Please do as follows.
1. To get the TRUE or FALSE result, please copy the array formula below into cell I2 and press the Ctrl + Shift + Enter keys to get the result.
- To get the YES or NO result, please change FALSE and TRUE to No and Yes respectively in the formula.
- If you are using Excel for Microsoft 365, the following formulas can also help.
- The two ranges must have the same cell dimensions, otherwise the #N/A error value will be returned.
When we not only want to know whether two ranges are equal, but also the differences between the two ranges, the Select Same & Different Cells feature of Kutools for Excel can solve the problem easily and quickly.
Before applying Kutools for Excel, please download and install it firstly.
1. Go to Kutools tab, and click Select > Select Same & Different Cells.
2. The Select Same & Different Cells dialog box pops up. Follow the steps below.
- Select the two ranges without headers respectively in the Find values in and According to textboxes.
- Choose the Single cell option in the Based on section.
- Check the Different Values option in the Find section.
- Specify the type of marking the differences in the Processing of results section. Choose between the Fill backcolor option and Fill font color option, or pick them both. Here I choose the Fill backcolor option.
3. Click OK. The Kutools for Excel dialog pops up, showing 1 cell(s) have been selected. It means one difference between the two ranges.
4. Click OK to close the dialog.
Note: If the two ranges are equal, Kutools for Excel dialog box will show No match was found.