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

Hi, 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 isnotpresent, 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.

Regards

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History