Need help with Comparing Columns in Excel

Microsoft Office excel 2007 home & stude...
July 9, 2010 at 08:56:00
Specs: Windows XP
Hi, i have a list of companies in column A1-260. I have another list of companies in B1 to B185. I want to know the companies in coulmn A which are not in coulm B. I've tried the formulas in the forum but I did not get a good output. I'm a novice excel user so please advise, Thanks

See More: Need help with Comparing Columns in Excel

Report •

July 9, 2010 at 10:48:34

Enter this in C1
=IF(ISNA(VLOOKUP(A1,$B$1:$B$185,1,FALSE)),"Not present","Present")

This uses VLOOKUP() to search for the value in a cell in Column A, in the range of cells B1 to B185.
If the value is not present, the VLOOKUP() formula returns the #NA error value.
The ISNA() function returns True if a value is the #NA error, hence ISNA() returns True when the value in column A is not present in column B
The IF() statement then makes one of two choices based on whether the result returned by ISNA() is True or not
If the value is True, the IF() statement returns (in this example) the text "Not present"
If VLOOKUP() finds the value, there is no error, ISNA() returns False and IF() returns the other choice, (in this example) the text "Present"

When you have entered the formula in cell C1 drag it down to extend it to row 260.
Note the $ signs in the formula - they are required to ensure that the ranges the formula refers to, remain correct when it is dragged.

Hope this helps.


Report •
Related Solutions

Ask Question