IF function?

[Hutia]

Dear All,

I need to find out which people are in list A but not in list B. How could I do this? I guess I need to use IF function, but I am not very familiar with this and would appreciate someone's help.

List A is in cells A 1:328
List B is in cells B 1:438

I expected to have two the same lists so would like to find out which people are missing from list A.

Regards,
Hutia

 

[jmacleary]

Hi there. In C1 put this formula: =IF(ISNA(VLOOKUP(A9,B$1:B$438,1,FALSE)),A9&" is missing from column B",VLOOKUP(A9,B$1:B$438,1,FALSE)) and copy down to row 328.

 

[Hutia]

Hello and thank you for your reply. This showed only 8 people at the bottom but of the list. Is it possible in column C to get the list referring to the column B, and highlight (or write number 1) next to the name which does not appear in list A?

 

[jmacleary]

Ok. I got the formula wrong anyway but: try this =IF(ISNA(VLOOKUP(B1,A$1:A$438,1,FALSE)),B1&" is missing from column A",VLOOKUP(B1,A$1:A$438,1,FALSE)) - it was not clear which way round youy wanted the comparison. This will compare all column B and give you the message. If you just want a 1, then this is it: =IF(ISNA(VLOOKUP(B1,A$1:A$438,1,FALSE)),"1",VLOOKUP(B1,A$1:A$438,1,FALSE))

 

[nikio8]

in cell C1:
=IF(Countifs(A:A,B1)>0,"B in A","B not in A")
drag down

 

[Hutia]

Thank you both so much - both formulas work very well. You are both so clever
Have a good day

Regards,
hutia