Compare Ranges By Using An Array Formula

Category: Formulas | [Item URL]

In Excel, you can compare the cells in two ranges with an array formula. For instance, to see if all of the values in A1:A100 are identical to those in B1:B100, type this array formula:

=SUM(IF(A1:A100=B1:B100,0,1)) 

Note: This is an array formula and it must be entered using Ctrl-Shift-Enter.

The formula will return the number of corresponding cells that are different. If the formula returns 0, it means that the two ranges are identical.


Search for Tips


All Tips

Browse Tips by Category

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

© Copyright 2016, J-Walk & Associates, Inc.
Privacy Policy