Nested vlookup

My formula is:

=((VLOOKUP($W$5181,Download!$A$1:$X$3403,3,FALSE))+(VLOOKUP($W$5182,Download!$A$1:$X$3403,3,FALSE)))*0.001

The formula looks up the value in  "$W..."and returns the amount of orders. 
I alwayshave values for the first part of the lookup, but niot the second.

THe issue is that if there nothing to report back for the second lookup, it 
gives me an "#NA" errror instead of at least returning the value for the 
first lookup.

Any help?

Thanks,
TJ
0
tojo107 (21)
8/10/2005 8:42:02 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
1066 Views

Similar Articles

[PageSpeed] 55

Wrap both Lookups in their own ISNA() wrapper to trap for that error:-

=(IF(ISNA(VLookup1),0,VLookup1)+IF(ISNA(VLookup2),0,VLookup2))*0.001

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03

------------------------------�------------------------------�----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------�------------------------------�----------------



"tojo107" <tojo107@discussions.microsoft.com> wrote in message 
news:1C10817D-F795-4C8D-A083-3A8F8F6A0F9F@microsoft.com...
> My formula is:
>
> =((VLOOKUP($W$5181,Download!$A$1:$X$3403,3,FALSE))+(VLOOKUP($W$5182,Download!$A$1:$X$3403,3,FALSE)))*0.001
>
> The formula looks up the value in  "$W..."and returns the amount of 
> orders.
> I alwayshave values for the first part of the lookup, but niot the second.
>
> THe issue is that if there nothing to report back for the second lookup, 
> it
> gives me an "#NA" errror instead of at least returning the value for the
> first lookup.
>
> Any help?
>
> Thanks,
> TJ 


0
ken.wright (2489)
8/10/2005 10:06:32 PM
Reply:

Similar Artilces:

Charting with missing data or empty strings from vlookup()
I've used the vlookup() trick -- if(isna(vlookup(...)), "", vlookup(...)) to construct an intermediate table, which I would like to chart. However, charting cells which contain the empty string ("") works porly. I really don't want to treat those missing values as zeros, if there's any way to avoid it -- I just want the chart to treat them as missing. How can I change either the way I'm using vlookup() or the way I'm charting to allow me to chart this data with missing values? Thanks for your help, Mike Ironically, if you'd left the #N/A in t...

Array Function to do a Sum of VLookUps that translate a letter gra
Suppose I have letter grade data in a range of cells, B12:D12 as follows A-, B-, B+/A- I have another range of cells, named GradeList that gives numerical equivalents for each of these letter grades. I want a function that will look up each of these grades in the table, and return their sum, average etc. I tried the following as an array formula: {=SUM(VLOOKUP(B12:D12,GradeList,2,FALSE))} And it didn't work; looks like it just returns the value of the lookup of B12. Actually, I want this formula to sum the highest N grade values; I know how to use an array formula to ...

Vlookup return 2nd match
I have a quick question for you seasoned Excel gurus... I use Excel to track win/loss streaks across several major league baseball teams. I then consolidate them on one sheet with game date on the left, and the vlookup results from the detail sheet for each team. I was doing fine, until April 27th when the Mets and Dodgers played a double-header, and now had 2 game detail data for the one date. I know I can use a Countif(A1:A100, A2) to determine if I have multiple dates, but don't know how to return the first true on one line, and the 2nd true on a second line. Any idea...

Vlookup, Index or Match
I posted this yesterday ... could not see ... so reposting it again Hello All, I am using Office 2003 and have the following problem: Sheet PROD (figures for example purpose only- it can be numeric or alpha numeric) Col B Col H 1 Mar-07 123 2 xx xx 3 xx xx 4 Mar-07 345 5 xx 6 Mar-07 678 7 Apr-07 910 8 xx xx 9 Apr-07 122 .... .... .... Below there are other months also...Col B will go down for a complete year. i.e. probably 500 Rows down. I have a Sheet name REPORTS where I wish to have the information from Sheet PROD when I input the Month ...

VLookup not working
Can anyone come up with a reason why a vlookup would not work ?? I have part numbers and sales category on one workbook. I have part numbers and prices on another workbook. I am just trying to pull over the sales category from the one workboo onto the one with the part number and prices. Now if it hadn't worked at all i would say its a mistake of mine... but it worked for over half the part numbers..... and yes the ones tha didnt work are on both of the workbooks ??????? ARG ??????? -- Message posted from http://www.ExcelForum.com Hi! Can you post the formula you are using and gi...

Vlookup formula where lookup value is a result a formula (receive.
Vlookup formula where lookup value is a result a formula (receive #N/A error) How fix? Without seeing what your formula is, it's impossible to do more than guess... In article <1B061D63-FA8B-4C96-BF0B-C3B706433CCF@microsoft.com>, cpitta <cpitta@discussions.microsoft.com> wrote: > Vlookup formula where lookup value is a result a formula (receive #N/A error) > How fix? Maybe: =if(iserror(vlookup(...)),"some error message",vlookup(...)) or maybe... =if(isna(a1),"",vlookup(a1,sheet2!a:e,3,false)) cpitta wrote: > > Vlookup formula wher...

Vlookup from a drop down list
In Column A I have a drop down list from A17:A46 In Column B I have a formula =IF(A17="","",VLOOKUP(A17,'Hidden Pg for lookup Price'!A1:C29,2,FALSE)) this formula works for just the 1st row but if I copy it down it accelerates the rows =IF(A18="","",VLOOKUP(A18,'Hidden Pg for lookup Price'!A2:C30,2,FALSE)) I tried to put $'s around the $A$1:$C$29 but it then seems to freeze it up and will only show me the formula in the cell and not give me a result? Any ideas? Thank you!!!! <I tried to put $'s around the $A$1:$C$29...

Combining SUM Function with Nested If Statement
Is it possible to combine a SUM Function with an IF statement that will allow me to add multiple cells, but leave the cell blank if the answer is zero? Here's what I tried, but to no avail. In cell F5, I have the following formula: =(SUM(C5:E5),IF(F5=0,"",(SUM(C5:E5))) Is there something wrong with the formula, or is the combination of functions/statements not allowed? Any help would be greatly appreciated. Sincerely, Josh =IF(SUM(C5:E5)=0,"",SUM(C5:E5)) Regards, Peo Sjoblom "Somecallmejosh" wrote: > Is it possible to combine a SUM Function wit...

vlookup question #3
My wife owns a t-shirt company and I'm trying to help her write a spreadsheet that will calculate price per shirt printed using a vlookup formula. The table has 10 rows of quantities in the left column and 6 colums of colors 1-6 across the top row. As long as I only use one vlookup (for a one sided print job) it works perfectly. Where I'm getting stuck is when I try to add a second vlookup for the number of colors to be printed on the back of each shirt. As long as I have a quantity of 1 to 6 it works fine. It's when the value is 0 or blank that something is going very wrong. Th...

Vlookups using 2 Values
Hi I am trying to use 2 separate cells of data to get a value in a vlookup. =VLOOKUP($D119119+$J119119,Lookup!$J$2:$L20,3,FALSE) - formula I am using Is the plus sign correct? When I use the + sign it is adding up and giving me a number when d119119 is a date and j11919 is a number Date Shift Letter 2/8 1 C 2/8 2 D 2/9 1 C 2/9 2 D 2/10 1 A 2/10 2 B 2/11 1 A 2/11 2 B Using info from the date column and shift column to get the info from the letter column. 1 and 2 are shifts for the day. Thank You I hope someone knows how to No. The symbol to use when you're conc...

[XSLT] How to obtain nesting level?
Hi, I have several nested layers of <node> element that are processed by an XSLT template. Is there any possibility to determine the depth of a node in the overall nesting hierarchy? For example, I would like to obtain 0 for the root <node> tag; 1 for all child <node> tags; 2 for all tags that are child <node> tags of all previous <node> tags (those of level 1); and so on. Is there any way to do this? Many thanks, Phil Philipp Schumann wrote: > I have several nested layers of <node> element that are processed by an XSLT > template. Is the...

Cannot use vlookup function
Hi I cannot execute the vlookup function on my excel but on other peoples machine it works fine -- B MLUNGWANA MCP ,MCP+I ,MCDST ,MCSA 2000 Sorry, but this newsgroup is for questions about Access, the database product that's part of Office Professional. I'd recommend reposting your question to a newsgroup related to Excel. When you do repost, it may be useful to the responder to know what version of Excel you're using (including which service packs have been installed), as well as what operating system. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele ...

Can a Vlookup be used when creating a chart
1. Is it possible? 2. where would it be entered? the values line? 1. Probably 2. Not enough detail to provide even a guess. Gord Dibben MS Excel MVP On Tue, 26 Jan 2010 11:23:02 -0800, Confusicous <Confusicous@discussions.microsoft.com> wrote: >1. Is it possible? >2. where would it be entered? the values line? Here is my data: 2010 spreadsheet = Entities = A18:A45 Numbers = N18:N45 2009 spreadsheet= Entities A18:A41 Numbers = N18:N41 The reason I would like to have the Vlookup is because new data was entered in 2010 but if I just enter the ...

If no value found in vLookup, I need to return a NULL to the Cell
I'm trying to setup a Lookup Table and if the value is not found, I need it to return Null. The closest I can seem to get is it is returning #NA. How do I get around this? Example: Formula in cell C1 is =vLookup(A1,G:H,2,False) I've tried incorportating If Conditions, =If(Match; =if(vLookup ......nothing is working. Thanks for your help! You can't return a null, buy you can return a null string... =IF(ISNA(MATCH(A1,G:G,FALSE)),"",VLOOKUP(A1,G:H,2,FALSE)) Or, in XL07 =IFERROR(VLOOKUP(...),"") In article <1177081611.084201.120520@b75g2000hs...

Nested if 10 / Vlookup Therapist
I am working on a Scheduling program for a counseling center. Thi center has 10 + therapists and 20 + clients. The plan is for th manager to only have to enter the clients name into the therapist page and to have the therapists name auto populate into the clients page. can get this to work fine with 7 therapists (the limit for nested i statements) but I cannot figure out how to get vlookup or any othe function to work. ohh by the way the or() statements are used because there are possibilities for the clients name to be entere (CLIENT/CLIENTc/CLIENTp) Code ------------------- =(IF...

vlookup is not the answer
I have a list of parts that are in Kits (like a socket set). It is two columns A = PARTS and B = KIT. It is sorted by Parts. one part can be in more than one kit and that is what I need to know. example: PART KIT Part_A Kit_1 Part_A Kit_2 Part_A Kit_3 Part_B Kit_2 Part_B Kit_3 Part_C Kit_1 The above array is in a named range called KIT_DATA. And I was going to use vlookup untill I realized it would only give me the first kit. The above data is in Sheet2. Sheet1 lists all of the Parts. The objective is to insert a...

VLOOKUP for a range of data rather than specific values?
I'm hoping this is an easy one. I have a table with a list to lookup from which is like as follows. <�137.00 �2.30 �137.00-�234.99 �4.60 �235.00 - �292.99 �5.80 >�293.00 �6.95 I want to lookup a value from the right hand side based on where my value lies in the left hand side. Hope you can help me Thanks in advance Kind regards tHatDudeUK Create a table 0 2.3 137 4.6 235 5.8 293 6.95 assume that the table dimensions are A1:B4 then use a formula =VLOOKUP(Lookup_Value,A1:B4,2) ...

VLOOKUP Nesting Formulas #2
When I say "there" I mean the data range I'm searching. I'm starting t think that a vlookup isn't the way I should be going. The purpose o this formula is to tell me if a specific item number that is on specific purchase order is on a boat from China yet. Container PO# Part# Cases Pieces CAXU9144316 P900686 66701 215 2,580 P900608 68301 615 7,380 P900686 68301 1267 15,204 P900686 69501 74 888 P900686 77501B 9 432 P900686 77901 145 580 I could be looking for item #77001 that is on PO#P900686, but if don't specify in the formula both numbers have to exist...

VLOOKUP
When I query a datatable using VLOOKUP I get the answer returned fro the first instance of a name that appears albeit in the correct column If ABC is in row 2 with a value of 5 and also in row 3 with a value o 6, I get the reult 5 from row 2 in my formula. What I need is for formula that will sum all instances of the row name ABC so giving m the answer 11 (5+6). Ideally I don't want to go into VB scripting bu can handle nesting ormulae. I don't want to use dat>subtotals either a I need it to work 'behind the scenes' I am using Excel 2000. Any help would be gratefully receiv...

creating nested formulas from drop down box
Hi, I have cell A1 with a drop down box containing 26 available choices. B1 has the dollar amount matching to the choice in A1 using vlookup. E1 totals several cells including B1 together. I want F1 to look at A1 and either enter the number from E1 or NA. Here's billing example: A1=January , B1=$5 ,C1=$10, D1=$1, E1=$16 (total of b-d1) F1 is the column for January G1 is the column for February H1 is the column for March, etc If A1 = Jan, then F1 should be $16 If A1 = Feb, then F1 should be NA or $0 This is the formula that has been working so far: =IF((G2="...

VLookup between date ranges ?
A B 2010082 4/3/10 2010091 4/10/10 2010092 4/17/10 2010101 4/24/10 2010102 5/1/10 2010111 5/8/10 Sorry 'bout that. Somehow it got send too early. Here is my table representing A = Pay Period Week, B = Sat date of week begin. If I manually enter any date, e.g., 4/8/10, in I need the lookup to produce the A3, because 4/8 is in that week. 4/10 to produce A4, 4/16 to produce A5, etc. Something like if >= B3 but <b4, then A3. row A B 3 2010082 4/3/10 4 2010091 4/10/10 5 2010092 4/17/10 6 2010101 ...

Advanced Vlookup
Hi I have a spreadsheet which has customer names and data about them such as country name , state name and city name. It also has details about type of purchases made. In another spreadsheet i want to call certain other data about the customer using the filter criteria. But i dont want to use auto filters. In cell a1 of this second spreadsheet i type the country/state/town name. In cell b1 of this second spreadsheet i type the type of purchase. Now these purchase types also have sub classifications. E.g Electronic gadgets can be a classififcation and dvd player , cell phones etc can be su...

Sql and nested Xml-docs
Hi, I've used the in-built function in VS.Net to generate a schema from an existing (schemaless) xml-document. I then generated a DataSet class from this schema, basically to be able to work with the XmlDataDocument class. The (schemaless) xml-document describes a nested hierarchy,.e.g. <menu title="val1" role="val2"> <menuitem> <title>hubba</title> <url>www.hubba.com</url> </menuitem> <menu> and the schema loks like this (schema element removed for brevity) <xs:element name="menus"...

Nested DLs
Hi I'm not an administrator of our Exchange Server, but I do have permissions to manage some Distribution Lists. When I got these permissions I immediately restructured the DLs so that they are nested. Now I can't experiment with these addresses too much (as they are in use), but it appears to me that when the nesting gets to a certain level (maybe the third level) the user fails to receive the email. Is there an exchange setting that causes this (maybe one that prevents circular email sending when DLs refer to each other)? Hope you can help Kindly Simon Which Exchange version? ...

Nested class using MFC
Hello! I'm using a COM Dll and a COM client that I have copied from a book. This COM dll is using MFC and the COM client exe file is also using MFC. I have removed some code just to make it easier to figure out my problem. The code that I have removed does't affect the question in any way. In the code you have one interface called IVisual you have it below. struct IVisual : public IUnknown { STDMETHOD_(void, Display) () = 0; }; As you know in Interface IUnknown are there three pure virtual functiones named QueryInterface, AddRef and Release. The functions QueryInterface, AddRef...