# VLOOKUP problem

#### Jegacats

##### Board Regular
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

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
What is the vlookup looking-up in Spicework A1?

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.

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
Code:
``=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

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

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

Shaun

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

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.

Replies
8
Views
1K
Replies
2
Views
435

1,202,917
Messages
6,052,548
Members
444,591
Latest member
fauxlidae

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back