# How to Compare Columns in an Excel Spreadsheet

Written by ian low
• Share
• Tweet
• Share
• Pin
• Email

Columns can be compared in an Excel spreadsheet by using functions inside formulas in a third column to output the comparison result. Useful functions for comparisons are "EXACT," "MATCH," "FIND" and "IF."

## Comparing Values in Two Columns

If Column B and Column C contain monthly figures for different years, the year-on-year differences can be outputted in Column D by entering the formula "=C1'B1" in D1, then holding the SHIFT key while pressing the DOWN arrow to select the cells underneath, and then pressing "CTRL+D" to copy downward.

ColumnA ColumnB ColumnC ColumnD

Jan £65 £97 £32

Feb £130 £136 £6

Mar £195 £175 £19

## Error-Checking Two Columns

If the data in Column A and Column B are supposed to be the identical, discrepancies can be found in Column C by entering the formula "=EXACT(A1,B1)" in C1, and then copying downward to produce the result TRUE or FALSE for each row.

ColumnA ColumnB ColumnC

223 223 TRUE

133 134 FALSE

240 240 TRUE

## Data Validation Using FIND

Each cell in Column A can be validated against data listed in its adjacent cell in Column B by entering the formula "=IF(ISNUMBER(FIND(A1,B1)),"VALID","INVALID")" in Cell C1, and then copying downward.

ColumnA ColumnB ColumnC

JS JS,FJ,MC VALID

FJ JS,FJ,MC VALID

MC JS,FJ,MC VALID

HA JS,FJ,MC INVALID

## Data Validation Using MATCH

Column A can be validated against a list of data in Column B by entering the formula "=IF(ISERROR(MATCH(A1,\$B\$1:\$B\$3)),"INVALID","VALID")" in Cell C1, and then copying downward.

ColumnA ColumnB ColumnC

JS FJ VALID

FJ MC VALID

MC JS VALID

HA INVALID

### Don't Miss

• All types
• Articles
• Slideshows
• Videos
##### Sort:
• Most relevant
• Most popular
• Most recent