Locate the values in B not in A

[philky001]

I have a small worksheet: in A 176 and B 186. I would like to know which in B are not in A. Do I use vlookup? Below I would like a mark next to Gates, Bill and Green, Cole, or output a mark in col c is fine.
A B

fullname	staff_name
Almer, Harry	Almer, Harry
Aber, Nicole	Aber, Nicole
Apple, Steve	Gates, Bill
Apple, Tom	Green, Cole

 

[offthelip]

It is better to use match like this:

=IFERROR(MATCH(B2,A$2:A$176,0)>0,FALSE)

put this in row 2 column C and copy down

 

[Peter_SSs]

Along similar lines, if the name does match this identifies which row the (first) match is in, otherwise shows that it is not in column A.

22 01 17.xlsm
	A	B	C
1	fullname	staff_name
2	Almer, Harry	Almer, Harry	2
3	Aber, Nicole	Aber, Nicole	3
4	Apple, Steve	Gates, Bill	Not found
5	Apple, Tom	Green, Cole	Not found
Not in A

Cell Formulas
Range	Formula
C2:C5	C2	=IFNA(MATCH(B2,A:A,0),"Not found")

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[philky001]

this looks like it should work i see it now, thanks very much!

 

[Peter_SSs]

I want to find my match of A in B.

That is a different request to post #1. Have you changed what you want to know or is this an extra question?

Could you post a small set of sample data again and also include the expected results. Use XL2BB if possible.

 

[offthelip]

this looks like it should work but I think i need the vlookup. I want to find my match of A in B.

Both of the solutions suggested do exactly that, do try them, depends exactly what you mean by (match of A in B)
to find that macthc the other way round just swap the As for B in the equatoin

 

[jtakw]

Hi,

You can also do one of these:

Book3.xlsx
	A	B	C	D
1	fullname	staff_name
2	Almer, Harry	Almer, Harry	TRUE
3	Aber, Nicole	Aber, Nicole	TRUE
4	Apple, Steve	Gates, Bill	FALSE	Not in A
5	Apple, Tom	Green, Cole	FALSE	Not in A
Sheet942

Cell Formulas
Range	Formula
C2:C5	C2	=COUNTIF(A$2:A$176,B2)>=1
D2:D5	D2	=IF(COUNTIF(A$2:A$176,B2),"","Not in A")

EDIT: Post 3 to Post 6 were added before I posted.