LinkBack URL
About LinkBacks
Hi
Basically I have two lists of reference numbers. I need to find out if any of the reference numbers that appear in the first column appear in the second list.
The reference numbers may appear in their own list more than once.
Thanks
Jess
Hi
Basically I have two lists of reference numbers. I need to find out if any of the reference numbers that appear in the first column appear in the second list.
The reference numbers may appear in their own list more than once.
Thanks
Jess
Assuming data is in column A and B
in C1
=NOT(ISNA(VLOOKUP(A1,B$1:B$10,1,0)))+0
and copy down
0 means its missing
1 means its there
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
Hi thank you for your reply.
That didn’t work because it gives me a 1 for the reference numbers that appear in the same list. The reference numbers may appear in the same list but I don’t want to know that, I need to know if it appears in the other list.
Do you know how I could get that to work?
Thanks
Hi try C2:
=COUNTIF(A2,$B$2:$B$10)
copy down
Hi Thank you for your reply, this just gives me a list of 0
So none of the values in column A exist in B2:B10, then...?
Yes
I have tested it on a small sample of data
111 110 0
222 220 0
333 330 0
444 456 0
555 65 0
666 333 0
777 330 0
555 147 0
999 741 0
100 123 0
I get all 0's
Jess
Last edited by JessK; 09-03-2014 at 07:25 AM.
No, it will tell you how many times the number directly next to it (in column A) exists (anywhere) in the range B2:B10.
Sorry, I inverted the formula...:
=COUNTIF($B$2:$B$10,A2)
A vlookup would be the best option?
Assuming data is in column A and B,
=iferror(vlookup(1st cell column A,B:B,1,false))," ")
This will bring back nil values in columns where they do not exist in the second list.
Hopefully this helps.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks