help with vlookup

[ajcrew]

I have Column A that contains my list of storage tube IDs, then Column B that contains the IDs of tubes that have been removed from their box and used. The IDs in both columsn are out of order, so it is hard for me to mark off which ones of the tubes in Column A have been used by looking at Column B.


I want to use V lookup to do this. I need to know what tubes in Column B match those in A to note that they have been used and place that information in Column C. I don't care what the notation actually is in Column C, if it is "N/A", as long as I will know that tube from Column B matches Column A.

Can someone write the command for me?

[BeachRock]

Assuming you are just looking to find out if the cell in column A matches the cell adjacent to it in Column B, you could put the following into column C (then copy down) and it would return a 1 if the column A and B cells match and #N/A if they don't.

=MATCH($A2, $B2, 0)

You might want to attach an example of what you have.

[ajcrew]

Hi

No I can't use Match. It is not to match column A and B adjacent. Like I said, Column B has some tubes matching the same as column A but everything is out of order. I have even sorted both columns in Ascending order and still they do not align, so I can't use the MATCH value. It won't work.

I am pretty sure vlookup command - to take from Column B, then look into Column A (which contains the entire list). I have
put in the following command into column C, but it gives me a "REF!" error when I copy down: = VLOOKUP(A2,A2:A236,2,FALSE).

If vlookup isn't the best way, that's fine. But I hope it is clear what needs to be done now.

I can't upload the sheet, it is confidential.

Can someone help? I need to be done this before today ends. Thanks

[vlady]

i think beach rock is correct --use match..

B2 the number that was removed from their box.
find the number in B2 in the range A2:A236 then display "removed" --if found otherws.. ---"not removed"

C2=IF(MATCH(B2,A2:A236,0),"Removed","not removed")

[ajcrew]

It isn't working, the If, match. I don't know why.

I have attached the file, garbled the tube IDs to share with you all. I have also reversed the columns (taken column is column A) and my original list (template) is in column B. I think it will be easier.

I have manually done the first 3 to show you. On the taken list, I copied "299" (cell A2), and did a Find (Ctrl+F) on the template list, then marked a "1" next to it (column C) to show on the template list that this tube was used.

This is what I want excel to do automatically. I hope this is clear now what I want to see.

Can someone do this now? Thanks.

[vlady]

=IF(ISNA(MATCH(B2,$A$2:$A$236,0)),"not removed","REMOVED")

[BeachRock]

If I'm understanding you correctly, and there are no duplicates in columns A and B, then just having the value of column A show up in Column C if it exists in Column B should do what you need then. Right?

=IF(A2="","",VLOOKUP(A2,$B$2:$B$65535,1,FALSE))

and copy down.

Or use =IF(A2="","",VLOOKUP(B2,$A$2:$A$65535,1,FALSE)) to see if the items in column B exist in Column A. Returns Column B if it does.

[darknation144]

Did this solution work?