VLOOKUP problem

[Jegacats]

Hello
I am having trouble doing a VLOOKUP.
I have two tabs
On each tab, I have one coulmn.
The data on Both tabs are formated identically. Text arial 10
They are both sorted in ascending order
Here is how I do my VLOOKUP
=VLOOKUP(Spicework!A1;Caro!1:65536;2;FALSE)
On a third tab I want to see the the computer that are in both lists. When I do my VLOOKUP I get #NA or #REF. What am I doing wrong?

Thank you for any light you can shed.
tab 1 - Computer1
abc-053507
abc-053519
abc-053520
abc-053524
abc-053525
abc-053548
abc-053549
abc-053553
abc-053554
tab 2 -Computer2
abc-003211
abc-053519
abc-053549
abc-053556
abc-058528
abc-058556
abc-058558
abc-058611
abc-058633
abc-071027
abc-071095
abc-071228
abc-119003
abc-119009
abc-119046
abc-119059
abc-119062

 

[m0atz]

What is the vlookup looking-up in Spicework A1?

 

[RoryA]

If you get #N/A then there is no match. I suspect your #REF errors are due to tyou copying the formula down without making the row numbers absolute, but your formula does not need to look at entire rows anyway. You could just use:
=COUNTIF(Caro!$A:$A;Spicework!A1)>0
which will return TRUE if there's a match.

 

[ShaunD30]

Normally I would just do the Vlookup in one of the existing tabs and copy and paste the results in a new tab..But if you want the results in Tab 3 using Vlookup

Try this

=Vlookup(Spicework!A:A,Caro!A:A,1,FALSE)

a #N/A means there was no match.

Just drag the formula down as far as you need to.


Shaun

 

[ShaunD30]

**Shaun glances up at rorya's post**

Or you could do a Countif formula that might make more sense.

Shaun

 

[mooseman]

Another thing to check is if the two sets of data even match.
Ex: If "abc-053519" is in A2 in each tab, in an empty cell type in =IF(Computer2!A2=Computer1!A2,"YES","NO") . If you get NOS, then look at the cells. are there spaces at the end of one and not the other?
If you get #N/A, then do a text-to-columns on both tabs for column A, because you have a data problem.

ShaunD30's code works perfectly

 

[Jegacats]

Maybe it is not a VLOOLKUP I should be doing.

If you notice in tab 1(spicework) there is an item called abc-053519
In tab 1(Caro) the same item is there also.

In tab 3 I am trying to pick out the items that are in both tabs 1 and 2.

I thought a VLOOKUP might do the trick, but I am not sure anymore.

Thanks for any input.