vlookup question.

=VLOOKUP(D84,JobListValues,4)    

Works fine unless the value I want returned is a blank cell. vlooku
returns a 0 and not my blank.   So my question is how do I get my blan
cell to return in the vlookup instead of the vlookup result of 0.

Haven't used vlookup before.

:confused

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

0
11/25/2003 7:05:50 PM
excel 39879 articles. 2 followers. Follow

5 Replies
448 Views

Similar Articles

[PageSpeed] 30

IF(VLOOKUP(D84,JobListValues,4)=0,"",VLOOKUP(D84,JobListValues,4))

-- 

Regards,

Peo Sjoblom


"IowaBuckMaster" <IowaBuckMaster.xgrmm@excelforum-nospam.com> wrote in
message news:IowaBuckMaster.xgrmm@excelforum-nospam.com...
>
> =VLOOKUP(D84,JobListValues,4)
>
> Works fine unless the value I want returned is a blank cell. vlookup
> returns a 0 and not my blank.   So my question is how do I get my blank
> cell to return in the vlookup instead of the vlookup result of 0.
>
> Haven't used vlookup before.
>
> :confused:
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>


0
terre08 (1112)
11/25/2003 7:16:23 PM
If 0 isnt needed to be returned as an answer to the lookup you can use

=IF(VLOOKUP(D84,JobListValues,4)=0,"",VLOOKUP(D84,JobListValues,4))

Randall


On Tue, 25 Nov 2003 13:05:50 -0600, IowaBuckMaster
<IowaBuckMaster.xgrmm@excelforum-nospam.com> wrote:

>
>=VLOOKUP(D84,JobListValues,4)    
>
>Works fine unless the value I want returned is a blank cell. vlookup
>returns a 0 and not my blank.   So my question is how do I get my blank
>cell to return in the vlookup instead of the vlookup result of 0.
>
>Haven't used vlookup before.
>
>:confused:
>
>
>------------------------------------------------
>~~ Message posted from http://www.ExcelTip.com/
>~~View and post usenet messages directly from http://www.ExcelForum.com/

0
randrob (24)
11/25/2003 7:29:44 PM
=If(Len(VLOOKUP(D84,JobListValues,4)=0,"",VLOOKUP(D84,JobListValues,4))
Translation: If the length of the result is 0, return "", otherwise return
the value.

Note: some of the other responses I've seen to this posting will return an
empty string if the looked-up value is either blank *OR* zero. This version
returns an empty string only if the cell is empty.  If the cell contains 0,
it returns 0.

Hope this helps,
--
George Nicholson

Remove 'Junk' from return address.


"IowaBuckMaster" <IowaBuckMaster.xgrmm@excelforum-nospam.com> wrote in
message news:IowaBuckMaster.xgrmm@excelforum-nospam.com...
>
> =VLOOKUP(D84,JobListValues,4)
>
> Works fine unless the value I want returned is a blank cell. vlookup
> returns a 0 and not my blank.   So my question is how do I get my blank
> cell to return in the vlookup instead of the vlookup result of 0.
>
> Haven't used vlookup before.
>
> :confused:
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>


0
JunkGeorgeN (154)
11/26/2003 1:08:18 AM
I've seen =vlookup() return a 0 if the cell were blank, but never the other way
around.

=if(vlookup()="","",vlookup())

Is another way.

George Nicholson wrote:
> 
> =If(Len(VLOOKUP(D84,JobListValues,4)=0,"",VLOOKUP(D84,JobListValues,4))
> Translation: If the length of the result is 0, return "", otherwise return
> the value.
> 
> Note: some of the other responses I've seen to this posting will return an
> empty string if the looked-up value is either blank *OR* zero. This version
> returns an empty string only if the cell is empty.  If the cell contains 0,
> it returns 0.
> 
> Hope this helps,
> --
> George Nicholson
> 
> Remove 'Junk' from return address.
> 
> "IowaBuckMaster" <IowaBuckMaster.xgrmm@excelforum-nospam.com> wrote in
> message news:IowaBuckMaster.xgrmm@excelforum-nospam.com...
> >
> > =VLOOKUP(D84,JobListValues,4)
> >
> > Works fine unless the value I want returned is a blank cell. vlookup
> > returns a 0 and not my blank.   So my question is how do I get my blank
> > cell to return in the vlookup instead of the vlookup result of 0.
> >
> > Haven't used vlookup before.
> >
> > :confused:
> >
> >
> > ------------------------------------------------
> > ~~ Message posted from http://www.ExcelTip.com/
> > ~~View and post usenet messages directly from http://www.ExcelForum.com/
> >

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
11/26/2003 1:17:14 AM
True but you forgot a parenthesis

=IF(LEN(VLOOKUP(D84,JobListValues,4))=0,"",VLOOKUP(D84,JobListValues,4))

-- 

Regards,

Peo Sjoblom

"George Nicholson" <JunkGeorgeN@msn.com> wrote in message
news:uKifWh7sDHA.1060@TK2MSFTNGP12.phx.gbl...
> =If(Len(VLOOKUP(D84,JobListValues,4)=0,"",VLOOKUP(D84,JobListValues,4))
> Translation: If the length of the result is 0, return "", otherwise return
> the value.
>
> Note: some of the other responses I've seen to this posting will return an
> empty string if the looked-up value is either blank *OR* zero. This
version
> returns an empty string only if the cell is empty.  If the cell contains
0,
> it returns 0.
>
> Hope this helps,
> --
> George Nicholson
>
> Remove 'Junk' from return address.
>
>
> "IowaBuckMaster" <IowaBuckMaster.xgrmm@excelforum-nospam.com> wrote in
> message news:IowaBuckMaster.xgrmm@excelforum-nospam.com...
> >
> > =VLOOKUP(D84,JobListValues,4)
> >
> > Works fine unless the value I want returned is a blank cell. vlookup
> > returns a 0 and not my blank.   So my question is how do I get my blank
> > cell to return in the vlookup instead of the vlookup result of 0.
> >
> > Haven't used vlookup before.
> >
> > :confused:
> >
> >
> > ------------------------------------------------
> > ~~ Message posted from http://www.ExcelTip.com/
> > ~~View and post usenet messages directly from http://www.ExcelForum.com/
> >
>
>


0
terre081 (3244)
11/26/2003 1:17:57 AM
Reply:

Similar Artilces:

few basic (I hope) questions from a noob
Hi, It's been a few days since I began using MS VS.NET. I am trying to do a simple steganography project using MFC. I've got a few questions, I don't know answer to.. 1. I have a CEdit control, where I would like to display text loaded from a file. I've set multiline option on, the same with vertical scroll. I am using CString variable to load the text to it. Character after a character i am reading the file and appending it to the CString variable. But the text in CEdit doesn't seem to look even familiar to the text in file. Well, the text is the same, but there are ...

Client-Side Lookup Question
Hello, We have a routine that runs when certain attributes on the order are filled in. So when the attributes are filled in and the users press the save button a message pops up confirming that they want to run this routine. One of the citeria for running the routine is that the opportunity that the order is associated to must be closed. Right now there is no checking for this. Is there a way through jscript that when they try to run the routine it will first go out and check if the associated opportunity is closed. If it is still open it pops up a message saying they must close ...

Newbie Xpath Question #2
Hi everyone, i have a quesiton regarding XML/Xpath i have a XML document that has let say the following structure <cases> <case> <num>1<num> <detective> <name>John</name> <lastname>Smith</lastname> <detective> </case> <case> <num>2<num> <detective> <name>Roger</name> <lastname>Wilco</lastname> <detective> </case> </cases> I want to know, what would be the most "by the ...

Spam Question #2
Hi, This is a question about some messages I am getting from my server. It is a general SMTP and spam question. My boss keeps telling me - I never sent this message, but here's a message telling me it couldn't be delivered! I am wondering if these messages are actually being sent through our server, or somehow spoofed. Here's what makes me think they may be spoofed: Return-path: <here@us.com> Received: from [59.93.199.74] (helo=ilisa.com) by host28.ipowerweb.com with esmtp (Exim 4.43) id 1Ee7h6-0004U2-K6 for mpratap@progia.com; Mon, 21 Nov 2005 01:10:59 -0800 From: ...

a simple count question ... ?
I'm using Excel 2007 and in column A I have a list of items that I wish to have a total count of. I have spaces (empty cells) between some items in column A to make it easier to group (and to see) but was wondering if there's a way to count the cells that only contain info. In case I haven't described this clearly, I don't want an addition of these items (they're words and not numbers), I just want to know how many cells in column A contain data. Thanks, Dave Horne Hi Dave =COUNTA(A1:A100) Regards, Per "Dave Horne" <davehorne@home.nl> skrev i m...

VBA question
A co-worker, who has since moved on to greener pastures, coded the following for me. I use it to explode the contents of partlists, where all the part numbers of a particular type are stored in a cell. Now I am receiving the lists from new sources, and need to update the code a bit, something I am completely unfamiliar with. In this statement If InStr(strNewNumber1, " ") > 0 Then how would I also include (TAB) and other pseudo space charecters, so it will work on files produced by people who refuse to use the space bar? Function Explode() Dim strRow As Str...

Simple Public Folders question..
How do i prevent users (outside of administrators) from creating Public Folders or Public Folder items? Exchange 2003 native mode, all Outlook 2003 clients. Thanks. Wait, never mind. I found it. http://support.microsoft.com/kb/328808/en-us "jim" <jim@NOSPAM.com> wrote in message news:%23LvDOEmXGHA.1204@TK2MSFTNGP04.phx.gbl... > How do i prevent users (outside of administrators) from creating Public > Folders or Public Folder items? > > Exchange 2003 native mode, all Outlook 2003 clients. > > Thanks. > On Wed, 12 Apr 2006 15:10:17 -0400, &quo...

Problem with draging a formula,one cell value fixed,trivial question
Hi. I have a value in cell A10. Then i have a formula in cell C1 which goes: (A1+B1)/A10. If I drag the formula down to cell C5 the formula for C2 will be (A2+B2)/A11, for C3 will be (A3+B3)/A12,....,for C5 will be (A5+B)/A14. I would like the formula to have the A10 parameter fixed so when i drag the formula down the A10 would always be A10(it wouldn't go up by 1). So the formula for C2 will be (A2+B2)/A10, for C3 will be (A3+B3)/A10,....,for C5 will be (A5+B5)/A10. Please help. Thanks, Marko make your original in C1 formula =(A1+B1)/A$10 then drag down. See help on absolute versus re...

Clipboard question... CF_...what?
Hi, I understand this information is all out there, but it's all a bit confusing and I never know for sure when I read about it... I had a non-Unicode application and copied/pasted to/from the clipboard through CF_TEXT which went all fine. Now I moved to Unicode and need to update my copy/paste routines. I implemented copy and paste for CF_UNICODETEXT. But I have some questions: 1. When I look in the DataObject Viewer I sometimes see only CF_OEMTEXT and CF_TEXT on the clipboard (depending what the source was), but when I paste this contents in my application, I handle it through GetCl...

Publisher Question #4
I have created a poster/flyer but when I took it to printer they could not print the size I wanted, 24x30" because they said I needed to increase the size of what I placed on the disc. How do I do that, I have tried all i know I suspect you may have misunderstood what your printer said. What size is your original Publisher drawing? Maybe your design has a different width to height proportion than 24 x 30. -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "MsJLee" <MsJLee@discussions.microsoft.com> ...

vlookup on cell below
Is it possible to do a VLOOKUP but instead of returning the value in row that contains the lookup, it returns the value in the row below? eg a b 1 no x 2 yes y 3 ok z =VLOOKUP("yes",A1:B3,2,FALSE) would return "z" instead of "y". Th only way I can think of is to add a row header at the top and use the MATCH function in column A to find the row position of "yes", then use that in an HLOOKUP in column B. I was hoping there was a simpler way. Not VLOOKUP, but INDEX & MATCH =INDEX(B1:B3,MATCH("yes",A1:A3,0)) -...

2004 MSMoney Install questions
I just bought Money 2004 Deluxe and as I start the install, it starts installing IE 6.0 . I have and would like to keep IE 5.5 - any way around this ? IE 6 along with the additional features also seems prone to virus,spyware,hacks,bugs,crashes,etc - I have no problems with 5.5 and would like to stay at 5.5 . I aborted the install, so can you tell me any other MS "updates" they want me to install ? I currently am still at Win Media Player 6 and want to stay there too - the newer versions want to take over the PC. I prefer QT and Realplayer to WMP V7 and up. I have been using M20...

VB Formula / Vlookup
Hi I'm trying to create a macro that populates a cell in a worksheet with a vlookup formula, however, the table array is another workbook and is variable. here is my code so far... Let X = Range("factsaccount") & "." & Range("workstation") Range("NAV") = "=VLOOKUP(""Net Assets"",X$C:$D,2,FALSE)" Thanks -Dennis Maybe this technique will help: dim myRng as range set myrng _ = workbooks("otherworkbook").worksheets("otherworksheet").range("c:d") range("nav").form...

Commissions question
My company would like to start use commissions in great plains. However, our commissions model is that we recognize/payout commissions only when the payment is received from the customer. Can this be achieved in Great Plains. Our commission percentage is based on YTD sales by the sales person and are tiered. Can this be somehow accomodated in Great Plains as well. "Jack" wrote: > My company would like to start use commissions in great plains. However, our > commissions model is that we recognize/payout commissions only when the > payment is received from the custom...

Vlookup
Dear all, For vlookup, it will return the value of the destination cell. Is it possible to return the row no. instead of value of the destination cell?? Your help is highly appreciated. Thanks & regards, Automne Look at the MATCH function in Help. On Sat, 5 Mar 2005 01:41:39 +0800, "automne" <hersbt_no_spam@yahoo.com.hk> wrote: >Dear all, > >For vlookup, it will return the value of the destination cell. Is it >possible to return the row no. instead of value of the destination cell?? > >Your help is highly appreciated. > >Thanks & regards,...

CWnd creation question and CFrameWnd question
So you create a child window use CWnd::Create(). I can create a main window (windows window?) by using CFrameWnd::Create(). I'm trying to figure out how to create my own CFrameWnd from Cwnd and how they are related. Since CFrameWnd is a subclass of CWnd, shouldn't I be able to create a main window using CWnd::Create? It keeps asserting an error that I can't seem to trap (it just ends to app if I select Debug and if I step into it manually, I can hold down F11 for about a minute and it just keeps going through assembly code and such). I'm thinking because I send it NULL for...

Question about Exchange NIC usage???
Ill start off with the Spec of the Exchange server: Dual Xeon 3.4Ghz with 8Gb ram, and 4 300GB 10K in a raid 10 config. The logs are on 2 146GB 15K in a raid 1, Exchange 2003 SP2 Ok, we have something that appears to be a little strange. It appears that we are getting very little bandwidth response from the exchange server. Today i noticed it when i was setting up one of our VP"s new laptops. i was setting up her account, and the machine was barely pulling things from the Exchange server at l% usage? and it was very up and down? but never more than %2. We have a complete Gigabit ne...

vlookup-Closest value
Dear Friends, How can I use Vlookup to give me the closest value greather than or equal to vlookup value.For example if my lookup value is 5 and I have 4.9 and 5.1,5.2 It chooses 5.1 and give me the corresponding value. Thank you, atatari wrote: > Dear Friends, > > How can I use Vlookup to give me the closest value greather than or equal to > vlookup value.For example if my lookup value is 5 and I have 4.9 and 5.1,5.2 > It chooses 5.1 and give me the corresponding value. > > Thank you, VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) The default behavi...

IoC. A few questions ...
Hello, I am starting with IoC and I am creating modules as follows: private class DomainModule : NinjectModule { public override void Load() { Bind<IProductRepository>().To<ProductRepository>(); } } private class ApplicationModule : NinjectModule { public override void Load() { Bind<IEmailService>().To<EmailService>(); } } So when using IoC I suppose for all services and repositories I should have an interface, right? And should the interface be in the same namespace then the class itself? Should I separate maybe in ...

multithreading question
I drive my game's physics engine using a simple loop running on its own thread. It invokes Thread.Sleep(1) when processing takes little or no time (under 4ms) to allow real time to catch up. For heavier loads the loop uses bigger timeslices, up to 20ms, with no sleeps. Since this is a multithreaded application, I decided to break out CHESS to test the object (render) buffer for problems. But CHESS hates Thread.Sleep() - it fails any test that hits one, calls it a livelock. I ended up switching out the original realtime loop with a high resolution multimedia timer, but I ...

reformulating question regarding maximum value
I had an hour ago put a question up named "find maximum", but I had not formulated my query fully, hence this query. In each row, I want to find the second highest value in that row, and then as output give the value of that cell in the first row. For example: 1 2 6 8 6 5 4 3 2 4 5 6 3 2 3 8 6 4 5 3 7 2 3 1 The second largest value in the second row is nr 6. It is in the fourht cell of that row. The fourth cell of the first row is the value 8. This should be the output for the second row. The second largest value in the third row is the number 6. It is in the first cell of th...

Advanced Microsoft Exchange 2003 questions (POP3/SMTP/SPAM)
Hi! Can someone tell me if something like this is possible with an Exchange 2003 Server?: 1. Let say an Exchange 2003 Server is in a big company (e.g. company.com ;) ). All employees have a mailbox on this exchange 2003 Server. All the employees has also a mailbox on a POP3/SMTP server pop3.company.com / smtp.company.com . Can be an Exchange Server 2003 setup so that he automatically collects the mails from an employee pop3 account and put it automatically in his Exchange 2003 Server account??? So that the employee can only use the exchange account to collect all their mails and not from t...

Small Business 2006 Discount Question
I'm use to using quickbooks and it has a field on the invoices to show a discount given to clients or customers. How do I show this on Money? I'm sure they didn't fail to include a place for discounts. Can somone tell me how this is done? thank you Marilene ...

vlookup after conversion to 2007
We recently converted from 2003 to 2007. The vlookup formulas linking separate sheets no longer work. They return a blank space. Is there something I need to do to convert these functions? Thank you, April There is no reason why VLOOKUP formulas should not work in XL2007 Please show us a sample formula best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "April" <April@discussions.microsoft.com> wrote in message news:E7B859AA-76B2-4CE0-A985-BDDCF5D5B9CE@microsoft.com... > We recently converted from 2003 to 2007. &g...

Another Question lol. Formula this time.
Hi again, I have a worksheet that I want to highlight if there is a number greater than zero by changing the text colour. =if(a1>0)colour text red Any advice would be gratefully received Check out Conditional Formatting under Format. Cell value is > Greater than > 0 > OK HTH Regards, Howard "Nevyn" <Nevyn@discussions.microsoft.com> wrote in message news:3935B8CA-745A-4F5D-8504-B450B9E813C2@microsoft.com... > Hi again, > I have a worksheet that I want to highlight if there is a number greater > than zero by changing the text colou...