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.
Check if two ranges are equal with formula
Compare if two ranges are equal with a handy tool
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.
=IF(MAX(IF(A2:C8<>E2:G8,1,0))=1,"FALSE","TRUE")
Notes
- To get the YES or NO result, please change FALSE and TRUE to No and Yes respectively in the formula.
=IF(MAX(IF(A2:C8<>E2:G8,1,0))=1,"No","Yes")
- If you are using Excel for Microsoft 365, the following formulas can also help.
=AND(A2:C8=E2:G8)
=IF(AND(A2:C8=E2:G8), "YES","NO")
- 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.
How To Quickly Insert A Line Based On Current Date In Excel?
Supposing there is a sheet containing a row with dates, and now I want to insert a line right to the current date which will auto change while opening the workbook every day. Is there a trick can solve it in Excel?
How To Always Insert A Blank Row Below Instead Of Above In Excel?
As we all known, when inserting blank row in a worksheet, the blank row will always be inserted above the selected row or cell. But, sometimes, you may need to insert the row below the selected cell or row. How could you solve this job in Excel?
How To Insert A Blank New Row Automatically By Command Button In Excel?
In many cases, you may need to insert a blank new row into a specified position of your worksheet. In this article, we will show you how to insert a blank new row automatically by clicking on a Command Button in Excel.
How To Insert A Blank Row After Specific Text In Excel?
If you want to insert blank rows after a specific text as following screenshot shown, how to deal with it quickly and easily without inserting them manually one by one?
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
