VLOOKUP

Hi All

I have the following formula:
=IF($D$9="","",VLOOKUP($D$9,List!$A$1:$G$500,7,FALSE))
which works fine.

Problem:
How do I stop the value *0* displaying, if there is no data in
worksheet List! column 7 ?

Many thanks
George


0
3/1/2004 12:13:26 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
983 Views

Similar Articles

[PageSpeed] 41

George,

=IF($D$9="","",IF(VLOOKUP($D$9,List!$A$1:$G$500,7,FALSE)=0;"";VLOOKUP($D$9,L
ist!$A$1:$G$500,7,FALSE))


-- 
Regards,
Auk  Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

"George Gee" <george.nomaps@ntlworld.xom> wrote in message
news:Otk8AZ4$DHA.1796@TK2MSFTNGP12.phx.gbl...
> Hi All
>
> I have the following formula:
> =IF($D$9="","",VLOOKUP($D$9,List!$A$1:$G$500,7,FALSE))
> which works fine.
>
> Problem:
> How do I stop the value *0* displaying, if there is no data in
> worksheet List! column 7 ?
>
> Many thanks
> George
>
>


0
3/1/2004 12:40:17 PM
A.W.J. Ales

Many thanks, it works, although needed a slight change:

=IF($D$9="","",IF(VLOOKUP($D$9,List!$A$1:$G$500,7,FALSE)=0;"";VLOOKUP($D$9,L
ist!$A$1:$G$500,7,FALSE)))

George




*A.W.J. Ales* has posted this message:

> George,
>
>
=IF($D$9="","",IF(VLOOKUP($D$9,List!$A$1:$G$500,7,FALSE)=0;"";VLOOKUP($D$9,L
> ist!$A$1:$G$500,7,FALSE))
>
>
> --
> Regards,
> Auk  Ales
>
> * Please reply to this newsgroup only *
> * I will not react on unsolicited e-mails *
>
> "George Gee" <george.nomaps@ntlworld.xom> wrote in message
> news:Otk8AZ4$DHA.1796@TK2MSFTNGP12.phx.gbl...
>> Hi All
>>
>> I have the following formula:
>> =IF($D$9="","",VLOOKUP($D$9,List!$A$1:$G$500,7,FALSE))
>> which works fine.
>>
>> Problem:
>> How do I stop the value *0* displaying, if there is no data in
>> worksheet List! column 7 ?
>>
>> Many thanks
>> George


0
3/1/2004 12:54:15 PM
YW George and thanks for the feedback.
It's always nice to hear that things worked out.

-- 
Regards,
Auk  Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

"George Gee" <george.nomaps@ntlworld.xom> wrote in message
news:OiFX3v4$DHA.3828@TK2MSFTNGP10.phx.gbl...
> A.W.J. Ales
>
> Many thanks, it works, although needed a slight change:
>
>
=IF($D$9="","",IF(VLOOKUP($D$9,List!$A$1:$G$500,7,FALSE)=0;"";VLOOKUP($D$9,L
> ist!$A$1:$G$500,7,FALSE)))
>
> George
>
>
>
>
> *A.W.J. Ales* has posted this message:
>
> > George,
> >
> >
>
=IF($D$9="","",IF(VLOOKUP($D$9,List!$A$1:$G$500,7,FALSE)=0;"";VLOOKUP($D$9,L
> > ist!$A$1:$G$500,7,FALSE))
> >
> >
> > --
> > Regards,
> > Auk  Ales
> >
> > * Please reply to this newsgroup only *
> > * I will not react on unsolicited e-mails *
> >
> > "George Gee" <george.nomaps@ntlworld.xom> wrote in message
> > news:Otk8AZ4$DHA.1796@TK2MSFTNGP12.phx.gbl...
> >> Hi All
> >>
> >> I have the following formula:
> >> =IF($D$9="","",VLOOKUP($D$9,List!$A$1:$G$500,7,FALSE))
> >> which works fine.
> >>
> >> Problem:
> >> How do I stop the value *0* displaying, if there is no data in
> >> worksheet List! column 7 ?
> >>
> >> Many thanks
> >> George
>
>


0
3/1/2004 3:12:03 PM
Reply:

Similar Artilces:

Vlookup #4
I am using the vlookup function to look up data over several columns. If there is nothing in one column, I would like for it to return a blank cell instead of the 0 it returns now. Is there something else I can put in the formula to do this. Any help will be appreciated. Thanks in advance. Todd, Best thing to do would be to post the formula that you're using. You could test to see if the vlookup returns a zero and change it to a blank or you may just want to set your page up to not display zeros. Tools/Options/View & uncheck "Zero Values" John "Todd" <...

Vlookup Return Problem
Hi everyone, I'm new at excell and having a problem with a return on a vlookup formula. I have a workbook with several sheets all the same execpt worksheet 6. Which has my zipcodes, city and state. I want to type in zip and get the exact match and return the city. My formula looks like this: =VLOOKUP(P11,MyLookup,2,FALSE) H11 sheet 1 is where I have entered my formula and want my return to display P11 sheet 1 is where I'm entering my zip On sheet 6 which you see below is my range name MyLookup 2 is column B on sheet 6 False is exact Sheet 6 ...

Help using if condition and vlookup
Hi ... I have set of values in a column. using the if condition i need to lookup the values from one column and match with another column with adding up values for eg., Name Value A 25 B 15 A 15 B 30 C 20 D 25 C 10 please help in finding a solution using ISNA I think you mean =SUMIF(A:A,"A",B:B) -- HTH RP (remove nothere from the email address if mailing direct) <saraskandan@gmail.com> wrote in message news:1132760279.278844.197750@f14g2000cwb.googlegroups.com... > Hi ... > > I have set of values in a column. using the if condition i need to > lookup th...

=IF(NOT(ISNA(VLOOKUP Function
I am using Excel 2003 I am trying to produce a summary shett from five differnet worksheets. I am using the following function =IF(NOT(ISNA(VLOOKUP(E7,Tests!D1:Tests!S147,18,FALSE))),VLOOKUP(E7,Tests!D1:Tests!S147,18,FALSE),""). It works fine until I reach 18 and above and it returns #REF!. Does this formula not work after 17 or is there another formula that I should be using. Colin Hi, The VLOOKUP() table array cannot span across sheets. Do let us know what you are trying to do? -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com ...

blanks instead of "0" in vlookup
This is a vlookup: I am looking up a cell that happens to be blank, but the result of the vlookup is "0." How do I change the O to a blank? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200507/1 =IF(ISBLANK(vlookup),"",(vlookup)) Replace vlookup with your current vlookup formula. ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Patty via OfficeKB.com" <forum@OfficeKB.com> wrote in message news:5107F8367ABD0@OfficeKB.com... > This is a vlookup: > > I am looking up a cell that...

VLOOKUP #2
I'm having some trouble with VLOOKUP in Excel 2000 and after extensive Googling i'm none the wiser. We have an Access 2000 database, some data from which I am exporting into an XLS. I'm trying to x-ref this with a monthly Excel (not initially 2000 but later saved in that format) report, with the tried and tested method of adding a column of VLOOKUP(refno, namedtable, 2, FALSE) to link in a column of information from the database to relevant lines in the report. I'm certain i'm doing it correctly, but in some circumstances it seems two cells seem not to equate when they ac...

Error msg with vlookup
Does anyone know why I get an error #NAME? from this : activecell.formulaR1C1 = "= VLOOKUP(RC[-1],Sheet1!C[-1]:C[75],hdr,0)" hdr has been DIM as an integer. When running the value of hdr=33 If I put 33 in place of hdr qne run the macro, the lookup runs fine? Any help is appreciated -- KWB The variable "hdr" is named within VBA but you are posting a formula to a cell. The formula in some cell reads = VLOOKUP(RC[-1],Sheet1!C[-1]:C[75],hdr,0) but "hdr" is not a named cell or definition within Excel only within VBA environment. best wishes -- Bernard V ...

Distinguishing between upper & lower case characters for vlookup .....
Hi, Can someone please help me? ..... Scenario: Cell A1 contains the character A (upper case) Cell A2 contains the character a (lower case) Cell B1 contains 1 Cell B2 contains 2 In cell A3, I want to manually enter either character A (upper case) or character a (lower case) and then in cell B3 use a VLOOKUP function to return the value from column B corresponding to the character entered in cell A3. VLOOKUP function entered is =VLOOKUP(A3,$A$1:$B$2,2,FALSE). Question: Using the VLOOKUP function, the value that is returned in cell B3 when A (upper case) is entered into cell A3 is 1. The same...

Multiple criteria VLOOKUP
Hi, I have a seven column dataset (A1:G2000) and I want to lookup a value based on 3 criteria: one for each of the 1st three columns, so where the value in column A = X, column B = Y, column C = Z, with XYZ being three criteria defined by three other cells. Can anyone point me in the right direction. I'm wanting to figure out how to do it without creating a helper column where columns A,B and C are concatenated, and using the concatenating field as a lookup key. Any help greatly appreciated, Thanks....Jason Array formula =3DINDEX(D1:D10,MATCH("x"&"y"&...

Zero Values
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel When using Vlookup i get #NA returned in cells that have a number with a 0 in front. ie: 04011 returns #NA. I then try and type the number 04011 in the cell and find the 0 vanishes but the correct Vlookup result is returned. <br> I need the 0 to remain in the lookup value cell as it should be 04011, how do i get the 0 to remain when typed? <br> Cell formatting is GENERAL and when I change to NUMBER the correct result is returned however the 0 vanishes from the lookup value. <br> SHOW ZERO VALUES...

Vlookup and Data Validation Q
I am trying to use a DataValidation cell value to do a lookup and I get #N/a Cell N101 = a Data Validation list value Lookup formula in O101 = =VLOOKUP(N101,Sheet3!A$3:C$248,1,FALSE) Above returns #N/a, but the value I want is in Sheet3!A3 and N101 is in Sheet3!B3 What am I doing wrong? Vlookup doesn't work that way Use index/match to easily return results from a col to the left or right of the match col In O101: =INDEX(Sheet3!A:A,MATCH(N101,Sheet3!B:B,0)) -- Max Singapore "Seanie" <seanryanie@yahoo.co.uk> wrote in message news:7243d818-5359-4...

Application.VLookup and External files
I want to use Application.VLookup to find values in an excel spreadsheet on a network. I'm not sure of the syntax, especailly the " or ' and !. Here is what I have: In Sheet1 of Book3.xls I have this in a vba module: Sub FindPrice() cells(a,1) = Application.VLookup("PRM 8018539",'\\myserver01\PRICE UPDATES\[IPRICE 1.xls]'!sheet1.range("B2",Range("C65536").End(xlUp)),false) End Sub Exactly as is returns Compile Error: Expected: expression ant the first single quote in front of myserver. If I change both single quo...

VLOOKUP with 2 conditions
Hi there, Any ideas on how i can search based on two fields ? Scenario is as follows: Brian Red Fire Brian Blue Water john Red Volcano John Blue Hurricane I want to search For "Brian" which is equal to "Blue" and the answer I want to obtain is "water" Any ideas will be appreciated. I know you can concatenate Brian & Blue & apply a vlookup but i know thios can be done by the use of an array. Thanks in advance. Brian Hi try the following array formula =INDEX(C1:C100,MATCH(1,(A1:A100="Brian")*(B1:B100="Blue"),0))...

how to use a VLOOKUP function in a VBA code?
Hi am a novice in excel...i have a excel sheet with 2 columns with around 10000 records.i have placed a button in the excel sheet and on clicking the button it should find all the values in the first column which doesnt have a match in the second column.for that i can use VLOOKUP function.but i dont know how to use that one. Can any one help me? Thanks in advance.... With Regds Sunil.T WorksheetFunctions.Vlookup() The arguments are similar Mangesh -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.ex...

Using Symbols (&/-) in In Two Dependent Drop Down Boxes. Can't get the VLOOKUP to work!!!
Hey all, I have a category list of products and then a list of brand names for each product. So, I set up a series of drop down boxes (using Data Validation) for the categories and then a second series dependent which category you pick in the first, for the brand names. HERE IS MY PROBLEM: When I set up the category list I had to create them as One-Word-Names. I tried to set up a lookup table with the real category names (multiple words and symbols), but I can't seem to get the first drop down box to reference the lookup without then screwing up the Data Validation of the second (depende...

Looking for a function that performs a special kind of Vlookup
Let say you have a table with columns A, B, C, D, E, F and you want to do a vlookup, but not the regular vlookup but one that takes into account values in columns A and B. For instance: A B C D E F Day Tue 2 4 3 3 Day Wed 7 6 8 5 Day Thu 2 1 9 7 Taking the 2nd row, I want to do a vlookup based on Day and Wed instead of just using Day. Is there a function like that or any combination of functions that I can use to do this? You can use sumproduct to do something close to what you want. It is similar to a sumif ...

Lost on Vlookup, match, etc....
Can someone walk me through this please? I a workbook that imports a years worth of data from filemaker to be analyzed and charted in excel. It contains several sheets, but I am concerned with worksheet 1 (daily data) and worksheet 2 (bodyweight). Daily data contains the raw data I pull in from Filemaker. It results in a table with a row for each day of the year. It has 12 columns, but in this instance, I am only interested in 2 of the colums Column F, (Date), and Column R (Bodyweight). There is only one entry per week for body weight. The bodyweight sheet has 3 columns (week #, date, and w...

vlookup and if, I think
I have the following data: Client January February March (and so on until December) A 1 1 1 1 1 1 1 1 B 1 1 1 1 1 1 Each time I visit a client, I enter a 1 in the corresponding cell. I want to detail, in a separate range, how many times I visit a client in each month. Trying to clarify a bit more: I want to have a list in a cell with the clients, a list in another cell with the months, and when I choose for example A, February, to obtain a 3. Is this possible? Thanks in advance, Regards, Emece.- Assum...

vlookup using calculated references
Why can't vlookup use calculated references? One table has its reference values listed like 123-456 (the table from which values must be extracted). The other table has its values listed like 123456 (the table which dictates which values to extract). So I used =SUBSTITUTE to remove the "-" in the first table. Vlookup doesn't seem to be able to use the adjusted reference value in the first table. Is there a work around? Regards, Robin VLOOKUP can certainly use calculated references: =VLOOKUP(LEFT(A1,3) & "-" & RIGHT(A1,3), Table, 2, FALSE) finds ...

VLOOKUP ?
I use the VLOOKUP function a lot but was wondering if it were possible to use this when there are more than one cell I want to separately use data from. Currently I have: =VLOOKUP(B1,Sheet3!$A$1:$B$65,2,FALSE) but what I can't seem to do is then take the data from the $C$ column, thus avoiding the $B$ column =VLOOKUP(B1,Sheet3!$A$1:$C$65,2,FALSE) ~ doesn't work as this includes column B. What I have to do at the moment is copy/paste the columns 'C' and 'D' from the 1st sheet to two new sheets so the data is in column B Many thanks Keith (Southend) =VLOOKUP(B1,Sheet3...

Vlookup question #8
When pulling values through from a table array, is there any way that the formatting can be pulled through too, not just the value? (Office 2003) -- Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me) No, a formula can only return a value. If the format applied to the table was conditionally formatted, then you could apply the same conditions to the cell with the formula. Hope this helps. Pete On Feb 13, 8:34=A0am, Paul Hyett <vidcap...@invalid83261.co.uk> wrote: > When pulling values through from a table array, is there any way that >...

VLOOKUP #21
I have a VLOOKUP table referenced from one sheet to another in the same workbook EXCEL is acting strangely in that it refuses to return all the values from the LOOKUP table. I have checked the range and am satisfied that it's right VLOOKUP seems to stop looking without reason, or returns the wrong value, having done everthing OK up to that point Is there a limit to the number if rows in which VLOOKUP can operate The value is visible in the table from which I want it returned and is in the right range I don't get either an N/A, ERR or VALUE returned; the values are in the right s...

VLOOKUP #11
i am trying to use the vlookup function where the second parameter is a range of excel sheets .how do we define these sheets? do we have to make some links within these sheets.? it is returning invalid as the result. trying to match an order number from a range of sheets and extract the order time from these sheets to a order number in the other main sheet ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** JK, There are few Excel commands or functions that work across sheets. In many case...

vlookup formula errors
I am trying to enter a vlookup function into a cell to look at data in another work book, but after entering the initial lookup reference followed by the comma, I get an error message when I try to move to the look up range in the other work book. It may be some setting within the work book but i cannot figure out what. I have used vlookup on many occasions and have never seen this type of error before. What error message do you get? Pete On Jun 3, 5:01=A0pm, BG Mark <BGM...@discussions.microsoft.com> wrote: > I am trying to enter a vlookup function into a cell to...

Sum Vlookup where there are blanks
Hi Excel 2002 sp3. I have a sheet which has 7 tables (one per day of the week) in which a number of members of staff are listed. Not all the staff are in all of the days. I have a summary table at the bottom which looks up the name in each table and sums their totals. However, I get the infamous #NA where the lookup can't match the criteria. How can I avoid this? This is what I have (which works when the referrence cell is occupied);- =IF(ISERROR(SUM(VLOOKUP(B115,mon1,4,FALSE),VLOOKUP(B115,tue1,4,FALSE),VLOOKUP(B115,weds1,4,FALSE),VLOOKUP(B115,thur1,4,FALSE),VLOOKUP(B...