Excel – How to quickly check if two columns in Excel are equivalent in value

microsoft excel

I am interested in taking two columns and getting a quick answer on whether they are equivalent in value or not. Let me show you what I mean:

enter image description here

So its trivial to make another column (EQUAL) that does a simple compare for each pair of cells in the two columns. It's also trivial to use conditional formatting on one of the two, checking its value against the other.

The problem is both of these methods require scanning the third column or the color of one of the columns. Often I am doing this for columns that are very, very long, and visual verification would take too long and neither do I trust my eyes.

I could use a pivot table to summarize the EQUAL column and see if any FALSE entries occur. I could also enable filtering and click on the filter on EQUAL and see what entries are shown. Again, all of these methods are time consuming for what seems to be such a simple computational task.

What I'm interested in finding out is if there is a single cell formula that answers the question. I attempted one above in the screenshot, but clearly it doesn't do what I expected, since A10 does not equal B10.

Anyone know of one that works or some other method that accomplishes this?

Best Answer

This is the sort of thing array formulas were designed for. You just need one cell, one formula to give you a comparison:

=AND(A2:A11=B2:B11)

Enter the formula by pressing Ctrl+Shift+Enter. If done correctly it will appear as the following:

{=AND(A2:A11=B2:B11)}

EDIT:

For a case-sensitive comparison, the equals operator will not work. Instead, the EXACT() function should be used. Also enter this as an array formula.

{=AND(EXACT(A2:A11,B2:B11))}