Need help looking up data-vlookup isn't working

I have 2 worksheets.  Column A in sheet 1 contains values that I know are in 
column A of sheet 2.  I need only the rest of the values in sheet 2 (in other 
words, the values that are NOT in sheet one).


  Thanks!


0
Utf
5/24/2010 9:06:01 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
842 Views

Similar Articles

[PageSpeed] 25

Put this in Sheet2 cell B1 and copy down

=IF(ISNA(VLOOKUP(A1,Sheet1!A1,1,FALSE)),A1,"")

Vaya con Dios,
Chuck, CABGx3



"MMcGee" <MMcGee@discussions.microsoft.com> wrote in message 
news:8B3F7255-3691-4CE3-AB21-B625764F6A91@microsoft.com...
>
> I have 2 worksheets.  Column A in sheet 1 contains values that I know are 
> in
> column A of sheet 2.  I need only the rest of the values in sheet 2 (in 
> other
> words, the values that are NOT in sheet one).
>
>
>  Thanks!
>
> 


0
CLR
5/24/2010 10:04:15 PM
We can still use VLOOKUP() sort of in reverse.

Say the big list is in Sheet2 column A.  Enter:

=VLOOKUP(A1,Sheet1!A1:A100,1,FALSE) and copy down.  Formulas returning #N/A 
correspond to names not found in Sheet1
-- 
Gary''s Student - gsnu201003


"MMcGee" wrote:

> 
> I have 2 worksheets.  Column A in sheet 1 contains values that I know are in 
> column A of sheet 2.  I need only the rest of the values in sheet 2 (in other 
> words, the values that are NOT in sheet one).
> 
> 
>   Thanks!
> 
> 
0
Utf
5/24/2010 10:14:01 PM
My bad......
should have been

=IF(ISNA(VLOOKUP(A1,Sheet1!A:A,1,FALSE)),A1,"")

Vaya con Dios,
Chuck, CABGx3




"CLR" <croberts@tampabay.rr.com> wrote in message 
news:%23gWEQ04%23KHA.3840@TK2MSFTNGP02.phx.gbl...
> Put this in Sheet2 cell B1 and copy down
>
> =IF(ISNA(VLOOKUP(A1,Sheet1!A1,1,FALSE)),A1,"")
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "MMcGee" <MMcGee@discussions.microsoft.com> wrote in message 
> news:8B3F7255-3691-4CE3-AB21-B625764F6A91@microsoft.com...
>>
>> I have 2 worksheets.  Column A in sheet 1 contains values that I know are 
>> in
>> column A of sheet 2.  I need only the rest of the values in sheet 2 (in 
>> other
>> words, the values that are NOT in sheet one).
>>
>>
>>  Thanks!
>>
>>
>
> 


0
CLR
5/24/2010 11:41:50 PM
In sheet2 cell B1 apply the below formula and copy down as required...Will 
return names which are not there in Sheet1 ColA. 

=IF(COUNTIF(Sheet1!A:A,A1),"",A1)

OR ' handling blank entries in ColA

=IF(OR(A1="",COUNTIF(Sheet1!A:A,A1)),"",A1)

-- 
Jacob (MVP - Excel)


"MMcGee" wrote:

> 
> I have 2 worksheets.  Column A in sheet 1 contains values that I know are in 
> column A of sheet 2.  I need only the rest of the values in sheet 2 (in other 
> words, the values that are NOT in sheet one).
> 
> 
>   Thanks!
> 
> 
0
Utf
5/25/2010 5:05:01 AM
Thanks so much to everyone for your help!  It appears that Mr. Skaria's 
formula solved my problem.  Thanks again kind sir!

Regards

"Jacob Skaria" wrote:

> In sheet2 cell B1 apply the below formula and copy down as required...Will 
> return names which are not there in Sheet1 ColA. 
> 
> =IF(COUNTIF(Sheet1!A:A,A1),"",A1)
> 
> OR ' handling blank entries in ColA
> 
> =IF(OR(A1="",COUNTIF(Sheet1!A:A,A1)),"",A1)
> 
> -- 
> Jacob (MVP - Excel)
> 
> 
> "MMcGee" wrote:
> 
> > 
> > I have 2 worksheets.  Column A in sheet 1 contains values that I know are in 
> > column A of sheet 2.  I need only the rest of the values in sheet 2 (in other 
> > words, the values that are NOT in sheet one).
> > 
> > 
> >   Thanks!
> > 
> > 
0
Utf
5/25/2010 9:17:01 PM
Reply:

Similar Artilces:

Help needed with Macro to send data to another sheet/workbook
The spreadsheet I've created makes the user select various options from different drop down menus... once selected the data is displayed on another part of the same sheet. I have a macro in place to clear these cells when the button is selected, I will still be looking to run this macro also. I'm looking for help to create a macro that will allow this data to be transferred onto another workbook/sheet on a button click. The export of data must not overwirte the existing data on the other workbook/sheet and should start on a new line each time the button is selected. Anyone got any su...

Transfer data from another computer
Daughter gave me her computer which has Outlook 2003 on it, but it was never used. I use Outlook 2000 on my old computer. How do I transfer the outlook 2000 data file from the old computer to the new computer? Is the data file structure compatible? Posted here every day. The file you need is your Personal Folders file (*.pst) It's where all the mail, calendar, contacts etc are stored. Take a look at these pages for info on Outlook data backup or transfer: http://www.slipstick.com/config/backup.htm http://www.howto-outlook.com/Howto/backupandrestore.htm http://office.microsoft.co...

Money 99 Quit Working
Yes, still using 99. Was working just fine last week, but now get, "Microsoft Money has encountered a problem and needs to close. We are sorry for the inconvenience." View the error report and see, "AppName: msmoney.exe AppVer: 7.5.16.1110 ModName: unknown ModVer: 0.0.0.0 Offset: 7e312b59". Uninstalled and reinstalled from CD and still get same message. Tried downloading M2005 to convert files so I can upgrade and 2005 cannot restore back up file. Tried repairing back up file to no avail. Tax time cometh, so help would be greatly appreciated. ...

part 2 help creating a formula
here is the strapping spread sheet. I couldn't attatch both of them t one post. Do I need to convert the feet to inches in a hidden cell t make it look up the needed value and put the fraction in a cell alone Attachment filename: p & g 101 strapping.zip Download attachment: http://www.excelforum.com/attachment.php?postid=50101 -- Message posted from http://www.ExcelForum.com ...

need to change page numbers
I need to change the page numbers that are on my Master Pages. I've been continuing a pub in difft versions now I need to restart at page 1 but I'm on page 350. Help Insert, section. -- Mary Sauer MS MVP http://office.microsoft.com/ http://www.mvps.org/msauer/ news://msnews.microsoft.com "noedel" <anonymous@discussions.microsoft.com> wrote in message news:18d1a01c44c05$a10f2bd0$a401280a@phx.gbl... > I need to change the page numbers that are on my Master > Pages. I've been continuing a pub in difft versions now I > need to restart at page 1 but I'...

Autofill not working after upgrade do MS Exchange 2003
Dear Group! Over the weekend I upgraded one of my clients Exchange 5.5 installations to Exchange 2003. Because old exchange was not in the Active Directory I decided to use ExMerge to extract everything from the old server and then import into the new one (those were two different servers). So that's what I did. And there seemed to be no problem with any of those, however we got a small problem with autofill. Before it used to auto fill names when you started typing: "John Sm" it would complete to: "John Smith" pulling data from AD. It doesn't do it anymore. I googl...

exchange 2000 webmail (OWA) not working after ghost image
Hello, When our Exchange 2000 Server at work crashed last weekend I had to restore a ghost image of the server made about a week before that... everything works fine after restoring the image, but webmail (Outlook Web Access) doesn't seem to be working. Any reason why it shouldn't? I have searched google but found nothing on this subject. I'm new to exchange, so I really don't have a clue where to start looking. Is there someone who can help or maybe knows where to start looking? Best Regards, Does it give you an error? -- Hope that helps, Dan Townsend This posting ...

how to copy data in folder to ThumbDriv and use in other computer
I want to copy data in outlook folders of one computer(office unit) to be used or pasted and read to another computer (at home),pls kindly advice how to do it? ...

Macro help #13
I have a macro that does certain functions on startup of my worksheet, during startup it collects info from a main file then pastes that info into the worksheet. I was wondering if there is a line of code that selects yes when the message box comes up and asks if you would like to 'replace the contents of the desination cells'. Also is there a line of code that will select no when it asks if you would like to save the large amount of information on the clipboard. Application.Displayalerts will prevent these kinds of messages from popping up and will "usually" take th...

Trouble managing layout of multiple data containers on a page
When I enter new data on a page, it is always created in a new data container. These containers are all independent of one another, so they can overlap. I want to be able to insert new data onto a page and have OneNote automatically move down all the data below it (even if that data is in multiple containers). Also, is there a way to get OneNote to put everything on the page into a single container, so i don't have to deal with these problems? Thanks, Jonathan Jonathan wrote: > When I enter new data on a page, it is always created in a new data > container. Onl...

Looking for a EDB analysis tool
I am looking for a tool capable of displaying the space not in use inside an EDB file. Have anyone seen a tool like that? Event Viewer? Event ID 1221 shows free space in a store after online defrag. -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------- "Alan747" <Alan747@discussions.microsoft.com> wrote in message news:15799D9B-9259-495E-923F-F5B3275424C5@microsoft.com... >I am looking for a tool capable of displaying the space not in use inside >an > EDB file. Have anyone seen a tool like that? "Alan7...

pls help one of my accounts does not recieve e-mail
i have variou accounts configured in my outlook express,and all of them recieve mails except one. i just would like to know if that is more likely to be a server's side problem or a client in this case my computer.... any imput would be much appreciated. thank u. ...

stumped on creating dependent data validation lists
I created 2 Data Validation lists with Indirect functions. On trying to use them in conjunction with a 3rd list, i realized my logic in trying to accomplish what i need had been flawed. The problem is that the 3rd list, which should be dependent on the 1st 2, changes location. That is, i have a very busy worksheet. In the main body of the worksheet, there are no empty rows and columns. But almost every cell needs to have dependent validation lists to select items from. The main body is from A13 to AW131. In the area E14 to Q131, I have the potential to need a dependent dropdown list i...

Excel 4.0 Macro help files for Excel 2002 do not work
Either from within Excel, or double clicking on the XLMACRO.CHM file from within Windows Explorer, I always get the same screen: Download the updated help screens. I DID THAT, AND IT MAKES NO DIFFERENCE! CAN ANYONE HELP ME OUT? Have I got the wrong files, or is some switch set wrong somewhere? I've downloaded, installed and added the excel 4.0 macro help functions that are necessary, and into the correct directory. It's located with all the other help files. (C:\Program Files\Microsoft Office\Office10\1033) I've downloaded from 2 update files, and neither makes a diffe...

Need to reinstall WinXP
Hi, I'm planning to reinstall Windows XP Home on my system as there are currently too many conflicts and speed problems. The last time I did this I seem to remember that I misunderstood the procedure I should follow to make backups of all my Microsoft Outlook data - and so I ended up losing all my appointments, reminders, tasks - and many, many important emails. So, instead of making the same mistake again I was wondering if people here would be able to offer me advice on making sure I (a) save all my Outlook data and (b) successfully import it into Outlook again when the reinstall ...

Macro Script help
Need some help please, somebody has provided me with the the following macro and I need to make a change but i'm not exactly sure how to implement the change. At present I am unable to get in touch with the person , that provided it and I need to try and get this all done this afternoon. The instruction was as follows : "you might have some unwanted spaces in at the begining or end of the data.Try altering the second line as shown below" How do i put this For Each Dn1 In Rng1 If Trim(Dn2) = Trim(Dn1) Then 'Alter this line into this, do i appe...

Looking up prices in Grid
Is there someone out there that can either help me with this or point me to an easy to understand website for help? (Wasn’t quite sure which forum to post this in. Sorry if it’s not in the correct one.) I am trying to figure out what price a client will have to pay based on the volume they will do and whether they use B/W or Color. It is based on the Grids below. For B/W, they will be using either ItemNo 21003 or 72027. For Color, they will be using either ItemNo 77100 or 77110. For example, Client is doing B/W and they do 1550 copies, the price for this job would be 0.26. Client ...

Lost of 10 hours work on a visio drawing... Please Help
Hy Following the abrupt switch off of my Lap top I cannot open any more a Visio 2000 drawing (the file .vsd is around 1.3MB)... The error message I got while trying to open is: " An error (100) occured during the action Open Save File" " Visio cannot open the file because it is not a Visio file" Do you know some tools that could help me recovering parts of this damage visio file? Thanks for your hep Alain79 Sorry, error 100 is fatal. The only way to minimize it's effect is by the use of backups or upgrading. John... Visio MVP Need stencils or ideas? ht...

Sales/Work Orders and/or layaways in MSPOS 2.0? Am I an idiot?
Running: MS POS 2.0 30-day TRIAL I manage a small retail & repair shop that often takes SALES/WORK ORDERS with DEPOSITS which are then finalized and made into PURCHASES several days later. In my present POS, Quickbooks POS 4.0 (which I hate), I just make a sales order and then convert it to sales transaction at completion. I have been UNABLE to find this type of transaction ANYWHERE in MSPOS 2.0. I search HELP for sales orders and all I get is PURCHASE ORDERS info. I search LAYAWAY and NOTHING comes up. Am I an IDIOT for not knowing where this most basic of functions resides? Surel...

Creat mailbox error ! Please help
I have this problem and i woud realy need some help, thank you : I am trying to create an user account in Active Directory so i can have in Exchange the email adress. Everything worked perfect until 2 days ago, when i was trying to do that, the user was created ok, i got no error, the user also can log in the domain, but in Emal Adress TAB the mail was not created. If i`m trying in email adress tab to create manually the mailbox it looks ok, but when cheking in Exchange there is no mailbox created and if i`m trying to send an email to that mailbox it says it doesn`t exist. If someone ...

form to enter Silent Auction data?
Does anyone have a form already set up to enter data for a silent auction? If so, how did you go about using it at check-out time? ...

help formula doesnt calculate
hi all my problem is as follows on entering a formula into a cell, the formula shows but it dosnt calculate result it just shows formula thanks alan Hi - check if the cell is formated as 'Text'. If yes change this to 'General' and re-enter the formula - goto 'Tools - Options - View' and make sure 'Formulas' is unchecked -- Regards Frank Kabel Frankfurt, Germany "ALAN EMERY" <alan-emery@blueyonder.co.uk> schrieb im Newsbeitrag news:c95Uc.1290$Cd5.15717465@news-text.cableinet.net... > hi all > my problem is as follows on entering a formu...

Losing subscript/superscript! Help!
Hi all, Here is my problem: When I type "H2O" in A1 cell (with subscript "2"), I found that if type "=a1" in B1 cell, it will only give back "H2O" (no subscript!). I know direct copying from one cell to another is okay, but is ther any other method to keep the subscript? Thanks! George Li -- hello_lp ----------------------------------------------------------------------- hello_lpc's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3130 View this thread: http://www.excelforum.com/showthread.php?threadid=51172 A formula c...

Vlookup Error
Hi, I am doing a vlookup using vbs and receiving an error with the formula. I believe it may be syntax but not sure. I am using 2007 but it also needs to run with 2003. Any help would be appreciated. workbook = Br 31 December 2009.xls worksheet = 09 December 2009 range = A1:K810 the column that I am looking for matches is in Column K and I am only looking for exact matches =VLookup(3948974,'[Br 31 December 2009.xls]09 December 2009'!$A$1:$K$810, 11, false) -- Thank you Dave Dave, Try this, works in both E2003 & E2007 myval = Application....

Some basic excel help.
I have tried a number of methods to solve 2 problems and just cannot get it right, I thought conditional formatting might do problem 1 but no joy, so I would appreciate any guidance . 1) I have a basic spread sheet which is as follows. Hours Rate Total Acuum Row 4 would read the total (D4) as =sum( b4*c4) and the accum(E4) would read =sum(e3+d4) So I have a number of hours multiplied by a variable rate which is entered into the total cell for each row., this value is added to the previous rows accum (total) to give a new ( running ) accum ( total ) on the row. Each row therefore has H...