Vlookup returns "0"

I am using the following formula
=VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE 
and it is returning a zero if there is no data found in 
that cell.  If there is no data found I would like it to 
display nothing.
How can I do this?
0
anonymous (74722)
9/16/2004 1:33:47 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
805 Views

Similar Articles

[PageSpeed] 2

Carolyn,

There are a couple ways so do this.  One is to test it, which makes for
doing the VLOOKUP function twice.

1)
=if(VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE)=0, "" ,
VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE))

This gets a little messy.  Better yet, and simpler is to hide the original
cell (column, whatever), and refer to it in another column.  If your
original formula is in B2, put this in another cell:

=if( B2 = 0,  "",  B2).

Now hide column B, and let this one display.

2) Simply format the original formula (Format - Cells - Number - Custom)
with

General; General;;General

The third term is nothing, so when it yields a 0, you get nothing.

3)

Use Tools - Options - View - deselect "zero values."  This will apply to all
cells in the worksheet with 0.
-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Carolyn" <anonymous@discussions.microsoft.com> wrote in message
news:05fa01c49b8d$3610a110$a301280a@phx.gbl...
> I am using the following formula
> =VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE
> and it is returning a zero if there is no data found in
> that cell.  If there is no data found I would like it to
> display nothing.
> How can I do this?


0
nothanks4548 (968)
9/16/2004 2:06:16 AM
Carolyn-


Here are a couple of approaches:

1.  Wrap your VLOOKUP in an IF() function:
	IF(VLOOKUP(A1,E1:H1,2,FALSE)=0,"",VLOOKUP(A1,E1:H1,2,FALSE))

2.  Use conditional formatting:
	Format -> Conditional Formatting...
	Cell value is | equal to | 0
	Click on 'Format' and set the text color to white (or
	whatever the fill color for the cell is.)

I use both of these methods all the time - VLOOKUP is one of my most 
frequently used functions.



Mike Argy
Custom Office solutions and
Windows/UNIX applications



Carolyn wrote:
> I am using the following formula
> =VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE 
> and it is returning a zero if there is no data found in 
> that cell.  If there is no data found I would like it to 
> display nothing.
> How can I do this?
0
Mike
9/16/2004 2:24:34 AM
I'd use this to check for an empty cell.

=if(VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE)="","",
    VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE))

Then if a 0 were returned, you know that it wasn't an empty cell--it was really
0.



Carolyn wrote:
> 
> I am using the following formula
> =VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE
> and it is returning a zero if there is no data found in
> that cell.  If there is no data found I would like it to
> display nothing.
> How can I do this?

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
9/16/2004 9:37:33 PM
Reply:

Similar Artilces:

Dynamics GP 9.0 Install for Client workstations
Very Disappointed. I created the msi file to send to the workstations. The client launched the file, it loaded Utilities, but needed the sa password to continue. I still had to go to the client's workstation to put this in. I also had to change the OLE notes and the Shared location of the reports.dic I was misinformed by Microsoft back in October 2005 that the installer package handled all of this information, thus making it a true client pushdown/pull. If it's a brand new install on a workstation, you must also create the ODBC client. Also, I cannot put anything from dis...

Numbers beginning with 0
Hi all! I'm having trouble figuring out what to do for a client. She has a speradsheet of addresses and unfortunately one postcode is 0200 (australian postcode). Of course when adding it into excel it obviously chops off the 0 and simply sets it to 200. When mail merging it is causing all sorts of grief and simply changing the format of the cell from number to text is apparently not helping. Is there a way to prevent excel from taking away the zero in a number formatted cell? Thanks in advance! How is she "adding" the post code to XL? If typing it in, preformat the c...

formula to return 1st of the month following 60 days
Hello - I need, please, a formula to return the following. So, if someone started working with us on 10/15/09, the 1st of the month following 60 days of employment would be 1/1/10. That's what I need. THANK YOU! What about 1st of a month say 10/1/2009 'If you expect 1/1/10 then use =DATE(YEAR(A1),MONTH(A1)+3,0)+1 'if you expect 12/1/09 then use the below version =DATE(YEAR(A1-1),MONTH(A1-1)+3,0)+1 Regards Jacob "Jessica" wrote: > Hello - > > I need, please, a formula to return the following. So, if someone started > ...

Charting with zeros or DIV/0 values
Hi - this might have an easy solution, but I'm a bit stuck. I've created a spreadsheet for data entry by another party. This data will be entered on a monthly basis and I only want to update it occasionally. So, I have control charts set up with the basic formulae (percent, mean, upper and lower control limits) in there. I was wondering if there was any way to have all of this data in the range of the chart without having the chart bottom out (i.e. plotting zeros). For example, my columns _might_ look something like this: Date.........Total...Defects...%ofDefects...Mean....UCL....

CRM 4.0 Activities Flaw/Issue
After upgrading from CRM 3 to 4 about 1 month ago, a sales rep pointed out an issue with Activites that isn't working as it should (or would think). In CRM 3, if you selected the Due Today option, it would list only activities due today. No overdue items. In CRM 4, if you select the Due Today option, it shows all activities due Today AND overdue. Basically, it's broken and useless with no Today option. In CRM 3, if you selected the Due Overdue option, it would list activites that are overdue. In CRM 4, if you selecte the Due Overdue option, it shows all activities due Today AND ove...

Unable to create new user on CRM 4.0
Hi everyone, I've a problem with my CRM 4.0 server. Since I use dcpromo on it (at first the CRM 4.0 server was DC and now it's only a server) I can't create new user on the CRM. The CRM works perfectly with the actuel user but I can't create/ reactive user... The message is in French, it's quite simple :( No info in the message, it just says me that an error has occured... I tried using the 2 methods in the CRM, it's the same error I don't know what can be the problem, and I don't now what do to ... I need help Thanks Potentially to do with Network Service a...

VC++6.0: Access violation while creating wizard with PropertySheet
I'm attempting to create a wizard using the tutorial at http://devcentral.iftech.com/articles/MFC/wizards/default.php. I've gotten as far as creating a dialog, associating it with a class derived from CPropertyPage, and adding that to a CPropertySheet dialog. Also, I call SetWizardMode in the appropriate place. It runs fine on XP. On ME, I get an access violation the SECOND time I run it, not the first. The accessviolation happens after calling CWizardDlg's DoModal method, specifically when the PropertySheet function is called. Once I get the access violation, I keep get...

Can XIRR start with a "0" in the first period
I am having issues calculating XIRR. When my first cash flow is 0, I get an error message, even though a regular IRR calc allows a 0 in the first period. Is this just the way XIRR works or is there a way I can get around it? Thanks for you help. carlsondaniel@gmail.com wrote: > I am having issues calculating XIRR. When my first cash flow is 0, I > get an error message, even though a regular IRR calc allows a 0 in the > first period. Is this just the way XIRR works or is there a way I can > get around it? This appears to be a defect in the XIRR algorithm. As you noted, IRR wo...

Changing default administrator account in CRM3.0
Hi, I want to know how to change the default administrator of the crm server Thanks in advance. -- Regards, Bishwarup Create a new user profile and assign that user the "System Administrator" role. Then log in to CRM as that user and revoke the "System Administrator" role from your current administrator. Dave "Bishwarup" <Bishwarup@discussions.microsoft.com> wrote in message news:D0C818F1-EDA4-4B7B-A254-A286CE3F9E1B@microsoft.com... > Hi, > > I want to know how to change the default administrator of the crm server > > Thanks in adv...

CRM 4.0 Campaigns`
Hi Just a simple quick question regarding campaigns. I have just created a test campaign to familiarise myself with the campaign feature. The question is when doing an email campaign when does the system send the emails as the status is sitting at Pending at the moment and nothing is happening. I am making a presumption that it does not happen till the evening as not to interfere with normal daily usage. I look forward to any comments. Thanks Hi David, First thing, do you want to send your email activities through Outlook or are you using a CRM Email connector to an Exchange Serv...

value from field not accepted within onlineform (3.0 -> 4.0)
Hy, We just tried to update our running CRM 3.0 to 4.0 there where nearly no problems! (1 report was not upgradeable) We where very happy. but right now a real problem was discovered: the fields: longitude and latidude from the contact form are no more accepted. Enter a value beetween .... But the values do perfectly match within the range When I delete the values I can save the form. When there is any value in the fields it causes the error: Enter a value ... Best regards for any help ...

Upgrade 6.0->7.5->8.0 with PA
I am conducting a test upgrade from 6.0 to 8.0 through 7.5 with Project Accounting on a test server. The PA conversion to 7.5 took about 11 hours which is manageable but the conversion to 8.0 has already taken 3 days! and is still running. It has spent most of the time on one particular task and I wondered if anyone else has experienced this and if there is any way to improve the situation. All I can see from SQL Profiler is that the auto-procedure zDP_RM00401SS_1 is being called for each row of that table in turn sometimes many times for the same row. There are about 28000 rows and I&...

loading 5.0 files to money 2003
I just got a new system with Money 2003 preloaded and when attempting to open my backup file(on a floppy) from an earlier version of money all I get is a message that asks if I want to back up to floppy. If I click on "dont back up" all I get is the message "money is not properly installed. Please try set up again" Any idea what I might be doing wrong/not doing??? Help thanks Is it possible that you can reload Money from any disks supplied to you with the system? -- Glyn Simpson, Microsoft MVP - Money Check http://support.microsoft.com/default.aspx?scid=fh;EN-...

4.0 Workflow
I am trying to setup a workflow to send an email when an Account record gets deleted. I want to include the name of the Company in the email and who deleted it. The workflow fires, but always has a Status of Waiting because it says "The requested record was not found or you do not have sufficient permissions to view it". The reason this happens, I am assuming, is that the record has already been deleted so data from the record cannot be included in the email. Is there a way I can accomplish what I want? Workflow plugins are asynchronous so by the time you get to the databas...

How do we close an invoice in version 3.0?
In version 1.2 we had an Action on the toolbar to close the invoice. Now we have nothing. I tried customizing the form and adding the statecode, but that field is Read-only and so it didn't help. We mass-print all of our invoices and need them to be closed (not deleted) once they are paid. HELP! Thanks, Karla So sorry!! I did not see Invoice Paid on the Actions menu once the invoice was open. Solved!!! "KK" wrote: > In version 1.2 we had an Action on the toolbar to close the invoice. > Now we have nothing. > > I tried customizing the form and adding...

i cannot send any emails through my CRM 3.0.
i cannot send any emails through my CRM 3.0. The strange thing is that i can only send emails to myself, but not anyone else. what should i check and where to detect the problem? Check the SMTP configuration on your CRM server. There is a good chance you chose the "default" SMTP setting during the setup and this requires SMTP to be configured on the server itself. You can run a "Repair" installation and it will allow you to change the SMTP config. -- Matt Parks MVP - Microsoft CRM "Maria" <Maria@discussions.microsoft.com> wrote in message new...

On Change is not firing In crm 4.0
hi I am facing problem Just i am writing alert like HI -on change of Bit field its firing when it Is radio button but when its check box it’s not firing what was the problem Please help me out I hate to not suggest this but I know I overlook the easy stuff sometimes but did you enable it? On the details tab you must be sure to select the Enable button. Any luck? Maybe this is the problem: with checkbox the onchange doesn't fire untill the field loses focus. So clicking the checkbox and then click any other place on the form might give the alert. Good luck, Bertil "Jack"...

Socket probem,GetLastError returns 10038 (Invalid socket handle)
hi all I am implementing SMTP protocol in MFC using Sockets. I am using the CSMTPConnection v1.36 code provided by PJ Naughter at http://www.codeproject.com/internet/csmtpconn.asp The problem is while sending mails sometimes mails are not sent. 1)I am connecting ,sending mail and then disconnecting. The above step is repeated 6 times hence 6 mails should be sent.Sometimes only 4 or 5 mails are sent.But sometimes all 6 mails are sent!!! The problem seems to be while Sending the MAIL command in SendMessage()function The return value of GetLastError() was 10038 i.e Socket operation on ...

J2EE to CRM 3.0
Does anyone know how to get a J2EE app to access data held in CRM 3.0? TIA -- Simon Morris CRM exposes two public webservices, one for the businesslogic and one for the metadata. You should be able to connect to those webservices using J2EE. -- Jeffry van de Vuurst CWR Mobility www.cwrmobility.com -- "Simon Morris" <SimonMorris@discussions.microsoft.com> wrote in message news:71EB635C-631C-49F7-A836-47900536CC64@microsoft.com... > Does anyone know how to get a J2EE app to access data held in CRM 3.0? > > TIA > -- > Simon Morris Hi Jeffry, Thanks for t...

GP Report Modifier Question (version 9.0)
On our invoice report I changed the format of a custom field to a custom format definition (that I named BB2 with 2 decimal places and show the currency symbol). Now our Inventory History Sales Summary Report prints all of the Total Quantities for the item with a currency symbol also, why is that? Is your Inventory History Sales Summary Report modified? Have you opened the field to check and make sure no format has been assigned to it? ...

CMemFile not working in VC++ 6.0 RELEASE mode
I am using VC++ 6.0 and have a multi-threaded application (exe & multiple DLLs) which seem to be having a CMemFile object getting clobbered when running in release mode. It does not happen in debug mode. When running in the debugger, the calling thread that builds the memory file reports: GetPosition = 0 GetLength = 228 ptr to memfile = aa2238 the method GetTextLineNOF is passed a ptr to CMemFile and reports: GetPosition = 0 GetLength = 228 ptr to memfile = aa2238 Which agrees as expected. ==== However when running in RELEASE mode: the calling thread that builds the memory file rep...

Mobile Express for Microsoft Dynamics CRM 4.0
Hi, The current release of Mobile Express for Microsoft Dynamics CRM 4.0 is available in English only. I need know when is available in Spanish? Hello, Mobile Express for other languages is now available. Please check the following blog posting for more information: http://blogs.msdn.com/crm/archive/2009/10/22/update-rollup-7-for-microsoft-dynamics-crm-4-0.aspx Thanks! -- ======================== Manoj Kithany [MSFT] Microsoft Dynamics - CRM This posting is provided "AS IS" with no warranties, and confers no rights. ======================== "Katyc" <Katyc@discus...

Importing Records in to CRM 3.0
Hello, On a monthly basis, I import a CSV into the Leads area of CRM. This worked well in 1.2 where I was able to select the "Target" field(s). When I tried this same technique in 3.0, I noticed the "Target" field contained the target field info, but was disabled thus not allowing me to change if I want to. Is this new in 3.0 and if so, is there a work-around Hi Kathy, I guess the target fields which are disabled must be mandatory fields for the lead. In V3 it was enforced that mandatory field has to be mapped and so this restriction. If right source field is ...

import store 2.0 to hq failing
After upgrading store ops from 1.2 to 2.0 (no problems) we are trying to implement hq 2.0 by bringing in the biggest store db through the hd admin. However, it fails on an invalid column category. Any ideas? ...

Include back ordered items on Pick ticket in 8.0.
A change was made to the printing of the Pick ticket in version 8.0. As part of that change the ability to print a pick ticket including back order items was eliminated. That flexability needs to be put back into the product. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the messag...