Vlookup returns a "0"

I am using a vlookup that works mostly as I expect. That is, when
there is a value to be returned, it returns the value - however, there
are instances where no value will be returned, in some cases, Excel
leaves the cell blank (as I prefer), in other cases, it populates the
cell with a 0 (zero).

What can I do to not have it populate with a zero? And why the
discrepancy between the two behaviors?
0
8/6/2008 3:40:20 PM
excel 39879 articles. 2 followers. Follow

5 Replies
765 Views

Similar Articles

[PageSpeed] 29

Please post your formula and examples of your table and search argument. How is the formula cell formatted?

-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel

<mark.wolven@gmail.com> wrote in message news:e84f3a3a-948b-4167-aef6-71a3761e384c@79g2000hsk.googlegroups.com...
|I am using a vlookup that works mostly as I expect. That is, when
| there is a value to be returned, it returns the value - however, there
| are instances where no value will be returned, in some cases, Excel
| leaves the cell blank (as I prefer), in other cases, it populates the
| cell with a 0 (zero).
|
| What can I do to not have it populate with a zero? And why the
| discrepancy between the two behaviors? 


0
nicolaus (2022)
8/6/2008 3:50:07 PM
On Aug 6, 11:50=A0am, "Niek Otten" <nicol...@xs4all.nl> wrote:
> Please post your formula and examples of your table and search argument. =
How is the formula cell formatted?
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> <mark.wol...@gmail.com> wrote in messagenews:e84f3a3a-948b-4167-aef6-71a3=
761e384c@79g2000hsk.googlegroups.com...
>
> |I am using a vlookup that works mostly as I expect. That is, when
> | there is a value to be returned, it returns the value - however, there
> | are instances where no value will be returned, in some cases, Excel
> | leaves the cell blank (as I prefer), in other cases, it populates the
> | cell with a 0 (zero).
> |
> | What can I do to not have it populate with a zero? And why the
> | discrepancy between the two behaviors?

I know that I can go to the Tools Options and turn off the "Zero
Values" and in this case, that is fine. But what if I want to display
zeros on the future.

OK, the formula in use is: =3Dvlookup($a26,NDOLU,4,false)
where NDOLU is a range defined as: =3DSLWInput!$A$2:$AX$340

And the fourth column in the range is formatted as "General" - the
column should be populated with a value of "Y" or be blank.
0
8/6/2008 4:01:53 PM
You can do it like this:

=3DIF(vlookup($a26,NDOLU,4,false)=3D"","",vlookup($a26,NDOLU,4,false))

That way if the return value is actually a zero rather than a blank
then a zero will be displayed. If you don't want a zero to be
displayed, then do it like this:

=3DIF(vlookup($a26,NDOLU,4,false)=3D0,"",vlookup($a26,NDOLU,4,false))

which will trap both blanks and zeroes.

Hope this helps.

Pete

On Aug 6, 5:01=A0pm, mark.wol...@gmail.com wrote:
> On Aug 6, 11:50=A0am, "Niek Otten" <nicol...@xs4all.nl> wrote:
>
>
>
>
>
> > Please post your formula and examples of your table and search argument=
.. How is the formula cell formatted?
>
> > --
> > Kind regards,
>
> > Niek Otten
> > Microsoft MVP - Excel
>
> > <mark.wol...@gmail.com> wrote in messagenews:e84f3a3a-948b-4167-aef6-71=
a3761e384c@79g2000hsk.googlegroups.com...
>
> > |I am using a vlookup that works mostly as I expect. That is, when
> > | there is a value to be returned, it returns the value - however, ther=
e
> > | are instances where no value will be returned, in some cases, Excel
> > | leaves the cell blank (as I prefer), in other cases, it populates the
> > | cell with a 0 (zero).
> > |
> > | What can I do to not have it populate with a zero? And why the
> > | discrepancy between the two behaviors?
>
> I know that I can go to the Tools Options and turn off the "Zero
> Values" and in this case, that is fine. But what if I want to display
> zeros on the future.
>
> OK, the formula in use is: =3Dvlookup($a26,NDOLU,4,false)
> where NDOLU is a range defined as: =3DSLWInput!$A$2:$AX$340
>
> And the fourth column in the range is formatted as "General" - the
> column should be populated with a value of "Y" or be blank.- Hide quoted =
text -
>
> - Show quoted text -

0
pashurst (2576)
8/6/2008 4:06:33 PM
You can do something like this

=IF(vlookup($a26,NDOLU,4,false)="","",vlookup($a26,NDOLU,4,false))

you can also use a custom format for this particular cell

General;-General;;

or if it is number format

0.00;-0.00;;

of course that will hide "real" zeros as well which the former won't

-- 


Regards,


Peo Sjoblom

<mark.wolven@gmail.com> wrote in message 
news:6711ec1d-5432-4edb-a661-15574ae6554d@26g2000hsk.googlegroups.com...
On Aug 6, 11:50 am, "Niek Otten" <nicol...@xs4all.nl> wrote:
> Please post your formula and examples of your table and search argument. 
> How is the formula cell formatted?
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> <mark.wol...@gmail.com> wrote in 
> messagenews:e84f3a3a-948b-4167-aef6-71a3761e384c@79g2000hsk.googlegroups.com...
>
> |I am using a vlookup that works mostly as I expect. That is, when
> | there is a value to be returned, it returns the value - however, there
> | are instances where no value will be returned, in some cases, Excel
> | leaves the cell blank (as I prefer), in other cases, it populates the
> | cell with a 0 (zero).
> |
> | What can I do to not have it populate with a zero? And why the
> | discrepancy between the two behaviors?

I know that I can go to the Tools Options and turn off the "Zero
Values" and in this case, that is fine. But what if I want to display
zeros on the future.

OK, the formula in use is: =vlookup($a26,NDOLU,4,false)
where NDOLU is a range defined as: =SLWInput!$A$2:$AX$340

And the fourth column in the range is formatted as "General" - the
column should be populated with a value of "Y" or be blank. 


0
peo.sjoblom (169)
8/6/2008 4:11:38 PM
To answer the other part of your question, I think that if the cell is 
truely empty then VLOOKUP() will return 0 but if it only looks blank because 
formula is returning "" then that "" will return the "" and the cell will 
look like it is remaining blank.

-- 
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


<mark.wolven@gmail.com> wrote in message 
news:6711ec1d-5432-4edb-a661-15574ae6554d@26g2000hsk.googlegroups.com...
On Aug 6, 11:50 am, "Niek Otten" <nicol...@xs4all.nl> wrote:
> Please post your formula and examples of your table and search argument. 
> How is the formula cell formatted?
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> <mark.wol...@gmail.com> wrote in 
> messagenews:e84f3a3a-948b-4167-aef6-71a3761e384c@79g2000hsk.googlegroups.com...
>
> |I am using a vlookup that works mostly as I expect. That is, when
> | there is a value to be returned, it returns the value - however, there
> | are instances where no value will be returned, in some cases, Excel
> | leaves the cell blank (as I prefer), in other cases, it populates the
> | cell with a 0 (zero).
> |
> | What can I do to not have it populate with a zero? And why the
> | discrepancy between the two behaviors?

I know that I can go to the Tools Options and turn off the "Zero
Values" and in this case, that is fine. But what if I want to display
zeros on the future.

OK, the formula in use is: =vlookup($a26,NDOLU,4,false)
where NDOLU is a range defined as: =SLWInput!$A$2:$AX$340

And the fourth column in the range is formatted as "General" - the
column should be populated with a value of "Y" or be blank.


0
sandymann2 (1054)
8/6/2008 4:24:34 PM
Reply:

Similar Artilces:

How to access table buffer in GP 10.0?
Hi, I am trying to access table buffer of table SOP_HDR_WORK attached to SOP_Entry form. But if I use the same piece of code I was using for GP 9.0 - if 'SOP Number' of table SOP_HDR_WORK <> "" then {some code} end if; I am not getting back any result. Is there any change the way we can access the table buffers in GP 10.0? Thanks. ...

Recurring bills in GP 8.0
I need someone to advise me on how to enter and post recurring bills as in utilities. I do not want to have to enter a bill each month and then go back and do a manual payment is there an easier way? Thank you, Shelly ...

GP 9.0 user logins passwords
We have this same problem, but only for a few users. A user will be told that their GP password is not valid for FRx, so the SQL admin will reset the FRx password. This allows the user to log into FRx, but on the next log in for GP, GP forces the user to change their password. Once the GP password is changed (so the user can get into GP), the FRx password again is locked and needs to be reset by the SQL admin. It is a vicious circle, so any help on this will be greatly appreciated. Again, it is strange because half of the users have no problem at all. TIA ryanb. ==== Newsgroups:...

Outlook and Proxy Server 2.0
I am trying to connect an Outlook client to a POP3 Server through a MS Proxy Server 2.0 and can not get it to work. What are the additional steps I need to take? ...

CRM3.0 to GP9 Integration
Anyone know if they have released the CRM3.0 to GP9.0 integration. I heard they released it, then pulled it. ...

VLOOKUP #40
Would like to post data from one worksheet to another. Could you provide a few more details about what exactly you want to do. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "dr" <drivera@opvista.com> wrote in message news:1f1001c52c08$ad4c33a0$a601280a@phx.gbl... > Would like to post data from one worksheet to another. ...

RSS 2.0 Extensibility
I'm wondering if any subscribers would care to offer brief comments -- pro *and* con -- regarding the fact that RSS 2.0 is now extensible, notably by using namespaces. <%= Clinton Gallagher NET csgallagher@REMOVETHISTEXTmetromilwaukee.com URL http://www.metromilwaukee.com/clintongallagher/ ...

Access is Denied in CRM 3.0 after upgrading from 1.2
After an upgrade of Microsoft CRM 1.2 to 3.0 without any errors, I can not log into the Microsoft CRM web page. I get the following error "you do not have enough privileges to access the microsoft crm object or perform the requested operation" I have searched everywhere for a solution for this but i am now at a loss. During the install i recieved two warnings 1. verify domain user account spn for the microsoft crm asp .net application pool account 2. setup was unable to verify that the "trust for delegation" property is enabled for the computer object. If I create a new...

Upgrade from 7.5 to 8.0 #2
I am upgrading from Great Plains 7.5 to 8.0. I received the following: Ugrade fail. HR_Inquiry HR_Tests Payroll Benefit Master UPR00600 Payroll Benefit Setup UPR40800 Payroll Master - Archvie UPR_MSTR Payroll Work Benefit UPR10205 How can I fixed these errors and continue my upgrade -- tk ...

#DIV/0! Error
Hi I get an error (#DIV/0!) when I divide one cell by the other and there are no entries in the cells. How can i get the folrmula to surpress the error (show a blank cell) when there are no entries in the cells that the formula refers to? Could you please provide an example Thanks John =IF(B1=0,"",A1/B1) -- HTH Bob Phillips "Joohn Calder" <JoohnCalder@discussions.microsoft.com> wrote in message news:4A736933-8B5E-4D25-A67F-AADE56803802@microsoft.com... > Hi > > I get an error (#DIV/0!) when I divide one cell by the other and there are > no ent...

CRM 4.0 Custom Contact View Problems
In our Microsoft Dynamics CRM 4.0 system we have created a custom view for the "Contacts" entity. One of the users of our system who has user privileges at the organizational level can access contact records when they search for them in the system; however, whenever they select the view, no records display. This appears to be a problem only for the user in question because other users in the system, myself included, can view records using the view. All permissions/user roles for the user in question appear to be fine, which is supported by the fact that they can access records when ...

Access 2.0 to Access XP?
Hello, My customer has an Access 2.0 database which they would like to upgrade to Access XP. Can I just use decompile/recompile/compact? Is it necessary to take any other steps? Thanks in advanc. Tracy No you can't just do that. Converting from A2.0 to any later version may involve a major exercise. Beyond writing a book, which you can buy, tell us what specifically is your problem? If you don't know A2.0, ie the "from" AND the "to", then you are not qualified to convert it, is my impression. Having said that, conversions are not too difficult if you jus...

Same query: Subform 0, combobox 1, wins by KO
Hello :) I have a table I want to modify but after it's filtered by an ID number. I have created a query that works great and added a listbox that displays the IDs and when I select the ID it requeries the query. Because I want the users to be happy, I have created a form (where the listbox is), and a subfom object that has created a subform where my query is ran. I then have a nice form with a datasheet display underneath to tick the checkboxes I need. BUT, the problem is that the subform displays the first ID correctly - but not the other ones. I have tried to link the WHERE to ...

Help with VC6.0 Basic Concept!
Hello everyone, I am new to VC,and I don't quit understand the defferences in project types in vc6.0, especially MFC AppWizard, Win32 Application, Win32 Console Application, Win32 Dynamic-Link Libray, MFC AppWizard and Win32 Static Libary,Thanks very much! Lazytiger wrote: > Hello everyone, > I am new to VC,and I don't quit understand the defferences in project > types in vc6.0, especially MFC AppWizard, Win32 Application, Win32 Console > Application, Win32 Dynamic-Link Libray, MFC AppWizard and Win32 Static > Libary,Thanks very much! A console applic...

Task Create - Status
I'm trying to make a simple task, when a user owner is assigned a task in CRM, it receives an email letting it know it has one. I have the following below, but it is not generating one when i assign one manually in the CRM. What to do? I've tried both the following: When task is created E-Mail to:[owner];Subject test As well as: When task is created if Task activity status = Open then E-Mail to:[owner];Subject test I've checked the mailserver many times over, and I can send emails manually from the CRM so you can rule that out Tested out ok on our end. Some ideas:...

CRM 3.0 to CRM 4.0
I would like to upgrade my CRM 3.0 to CRM 4.0, could any one give me advantages and new features in CRM 4.0 in order to convence my company to upgrade my CRM3.0 to CRM4.0. Currently we are using CRM 3.0 for Service Module only. Regards, SAB The workflow capabilities in itself are enough reason. All workflow is now managed from the CRM UI which includes triggering actions via update object "field change". In 3.0 you could only fire a workflow based upon an "On Create" event in the system i.e. New Contact, Invoice etc... In additiion, the 4.0 platform has given end ...

WSS 3.0 Move DB to Different SQL Server
Can some one provide me with the steps to complete this? Thank you, I have 4 DB SharePoint_AdminContent SharePoint_Config WSS_Content_1 WSS_Search_WEBSERVER I know to stop all the services and take db offline and then copy the file to the new server. Then attach them in the new Sql Server. But how do I tell WSS were all the db are now. ------=_NextPart_0001_AA8CBDCD Content-Type: text/plain Content-Transfer-Encoding: 7bit Hello Maida, You can use a SQL alias to redirect all SQL requests to the new SQL Server. Use Cliconfg on client machine to cofigure the alias....

compile error from vc6.0 to vc7.0
I developed a project in VC6.0. After installing VC7.0, I follow the suggestion to convert the previous project into VC7.0 project. Then, I compile the solution and got one compile error: can not open "fstream.h". It seems that VC7.0 has no fstream.h header file, is there any way to encounter this?Any suggestions are greatly appreciated. -- Yan ZHANG http://www.nict.com.sg/zhang/ >I developed a project in VC6.0. After installing VC7.0, I follow the >suggestion to convert the previous project into VC7.0 project. Then, I >compile the solution and got one compile error: &g...

RMS 2.0 Credit Cart Processing
Hello, I am looking to set up a new register with RMS 2.0 at a new location. I am currently using RMS 2.0 with Citi as my credit card processor but am looking for more processing options that can be integrated with RMS 2.0 without having to install additional software with decent rates. Seems like Citi has the best Debit/Atm fees but would like to know if there are a list of other processors out there that can be integrated with RMS 2.0. -- ~grace I don't really consider it installing new software, but it's sort of what you are doing when you go with Mercury, whom...

WSS 3.0 Event 3760
Brand new installation of WSS 3.0, WFE and SQL 2008 BE, everything is working correctly with the exception of the 3760 error every 5 minutes. The account trying to access the database is the server farm account but it is a different application pool it is trying to access. I have the server farm account unique and only being used by the central administration application pool. I created a 2nd web application with a different IIS site and a unique domain user account to run as the service. I've looked everywhere on my SQL server and SharePoint and cannot understand why the serv...

File Size Limit in CRM 3.0
Hey, A client of mine wants to increase their File Upload size. Right now the limit is 5MB (as it was in 1.2). Read an old post that said I could change the registry to increase the file size limit. However, this key does not seem to exist in the 3.0 version (or so says the client's network guy). Does anyone know how to change the file upload size limit? I know, I know, not a good idea, could drag down server performance, etc., but this is really what the client wants... -- --Dodd The key does exist. From the SDK: maxuploadfilesize Currently, the size limit for files uploaded to ...

Task groups in CRM 3.0
Hi, Is it possible to create Task groups in CRM 3.0, where in users will create tasks in that group which can be shared with the users of that group. We should be able to send the reminders for those tasks. My exact requirement is below: My team use tasks to track progress of reviews we do on brokers and coverholders but this is still being done outside BRM and using a shared task folder e.g. "dubs" in normal Outlook. This means that the contents are visible to all my team. Is it possible to set up task groups in BRM in the same format and could we have a diary system so we...

CRM 3.0 Training
Where's the 3.0 training on the PartnerSource site. All that I can see is the 1.2. Can you advise me as to where to find the right resources to get trained, and certified? Thanks, Richard Charlton Richard, I spent 2 hours looking last night, there is nothing up there yet. The only webcast I could find on v3.0 was for March 14th titled: Web Seminar: Notes from the Field: Microsoft Dynamics GP & Microsoft Dynamics CRM Technical Aspects of the New Integration Seems a shame to have such a huge product with no partner training. Mike "Richard" <Richard@discussions.mi...

Installation and Deployment Certification 4.0
Hello, I am studying to certification for CRM Installation and Deployment of Microsoft CRM 4.0. I would like to know tips to achieve my goals. Any help would be appreciated. Thanks in advance. MATTBIONDI read the Implementation Guide - specifically the Planning and the Installing documents. Dave Ireland "Juliano" <mattgame@uol.com.br> wrote in message news:9F6B0D99-BAE8-4BD1-ADBC-1CADEBF1A731@microsoft.com... > Hello, > > I am studying to certification for CRM Installation and Deployment of > Microsoft CRM 4.0. I would like to know tips to achieve my...

Is integration manager for GP 10.0 availabe?
Does anyone know if integration manager for GP 10.0 is available? If yes, where can I download it from? It's on the 2nd GP 10.0 install disk. "Nik" wrote: > Does anyone know if integration manager for GP 10.0 is available? If yes, > where can I download it from? ...