VLOOKUP problem

Jegacats

Board Regular
Joined
Jul 30, 2002
Messages
136
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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,151
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Board Regular
Joined
Jun 19, 2008
Messages
172
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
 

ShaunD30

Board Regular
Joined
Jun 19, 2008
Messages
172

ADVERTISEMENT

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

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

Shaun
 

mooseman

Board Regular
Joined
Jul 23, 2004
Messages
195
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

Board Regular
Joined
Jul 30, 2002
Messages
136
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,720
Members
414,401
Latest member
grenona2020

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top