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
1000 Views

Similar Articles

[PageSpeed] 14

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:

Nested IF & Nested AND
I'm trying to compare two cells to create two different outcomes. The formula I'm using is as follows; =IF((AND(H15="Capitol",I15="both")),F15/2,),IF((AND(H15="Capitol",I15="MPL41")),F15,) Cell H15 does equal Capitol, Cell I15 does equal both, Cell F15 does equal $3,094.36. When I use this formula I get the correct answer of $1,547.18 =IF((AND(H15="Capitol",I15="both")),F15/2,) When I try to nest another IF as shown above I get the error #value!. By adding the IF((AND(H15="Capitol",I15="MPL41")),F15,) I...

Nested Groups
We have nested groups (specialty areas) inside of a main discipline group. All groups are set to expand on the local server. We are experiencing issues with using this main group to assign permissions on Public Folders and to set it as allowed sender for message restrictions for other distribution groups. Has anyone come across this before? Does anyone know of a fix? Are these mail-enabled Security Groups? -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------------- "Heather Arnot" <HeatherArnot@discussions.microsoft.co...

Vlookup in Excel 2003
when I enter a vlookup formula into a cell...I hit enter and the actual formula stays in the cell...SHOWING. It doesn't give an error message but just displays the actual formula with no action...Help, please! Perhaps the cell was formatted as Text. Format to General then F2 and Enter to re-enter the formula. Or you are in Formula View and see all formulas as a string. Hit CTRL + `(backquote above Tab key) to toggle formula view on/off. Gord Dibben MS Excel MVP On Thu, 1 Apr 2010 13:54:01 -0700, Rich <Rich@discussions.microsoft.com> wrote: >when I ente...

Using Vlookup in VBA
In excel I have created a user form, in which there is a label ("nonfincalcLabel"). I am trying to change the Caption of the label based on a calculation using Vlookup when a change is made to one of the comboboxes. The lookup table range is defined as "nonfinRange" in the worksheet "nonfinTable". When i try to run the code it says, Run-time error '1004': Unable to get the Vlookup property of the WorksheetFunction class. Below is my code, where am i going wrong? Private Sub defaulthistoryCombo_Change() Dim searchRange As Range Set searchRange...

Nested IF
How can I overcome the limitation of having up to only 7 levels of nested IF functions? I want to display a name in a cell that corresponds to a number in another cell, and I have 16 numbers. e.g. =If(A1=1,"Name1",If(A2=2,"Name2",If(... how about using choose or vlookup =choose(a1,"name1","name2",etc) "Amjad" <amjadfarran@hotmail.com> wrote in message news:044b01c37255$9b8a13d0$a301280a@phx.gbl... > How can I overcome the limitation of having up to only 7 > levels of nested IF functions? > I want to display a name in a cell ...

How to create nested nodes in XML in C#
Hi, I have to create an XML in the following syntax: <RESPONSE> <QueueItems> <Node1>..... <Node2>..... </QueueItems> <QueueItems> ... </QueueItems> </RESPONSE> Iam building this through a SqlDataReader object. My problem is I cannot get DOM object to create the QueueItems node. Please help! TIA Ramya Ramya A wrote: > I have to create an XML in the following syntax: > > <RESPONSE> > <QueueItems> > <Node1>..... > <Node2&...

VLOOKUP with HLOOKUP
Hi, I thought this was simple and i could google it. but could not find a solution. Age 3 ( premium per years) 5 ( premium per year) etc etc 1 5000 7000 9000 2 3 4 etc in years A cell where i can enter AGE and another cell where i can enter 3 or 5 or etc years.. Format changed after i posted. What i want to do is..Eg, i want to look in column A for age and then corresponding values in the same row depending on premium ( years ) in B to F or xyz.. ...

Nesting
I have read several posts regarding nesting, but I haven't quite found what has perplexed me for the last couple of hours. I'm using a set of IF functions to check 12 different cells to see if they match a particular cell. If it does, it returns a value of a different cell, if false it returns zero. These are contained within a SUM function. Now, here is the perplexing part. They aren't "nested" per se, because they are all on the same level. I can get it to work by checking up to 11 of the cells, but on the 12th, it breaks. Here is the formula with only the 11 entries...

Nested groups
Hi, It seems to me W2K8 R2 has problems regarding file and folders permissions and nested groups. Our domain has a mix of W2K8 R2 and W2K3 DCs. From a RDP session on a member server itself, I try to browse to a folder where the local "Administrators" group has full control. The "Domain Admins" group is in the local "Administrators" group and my user is in the "Domain Admins" group. However, when I double-click the folder, I get this dialog box "You don't currently have permission to access this folder". If I click "C...

Excel
I am trying to create an "if" formula to check cells in a particular column for different texts, but it only lets me add 7 before getting an error that I have exceeded the limit for nesting in the current file format. How do I work around this? =IF(D4378="LUNCH/OFFICER",O4378,IF(D4378="INFORMATION",O4378,IF(D4378="BUSY",O4378,IF(D4378="DETAIL",O4378,IF(D4378="END OF DUTY",O4378,IF(D4378="EQUIPMENT",O4378,IF(D4378="FUEL",O4378,IF(D4378="LIDAR CALIBRATION",O4378,"")))))))) Try: ...

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...

Nested IF and VLOOKUP functions
I'm using the VLOOKUP function to return an exact value or #N/A, if no value is found. I am trying to use the IF function to then convert the error value of #N/A to a zero (0) where #N/A exists or just insert the appropriate number using VLOOKUP. I'm hoping that someone can help me identify where I'm going wrong. I am not experienced using Excel functions but have read up on the nesting, IF and VLOOKUP functions via HELP. I'm using Excel 2000. Here's what I have so far: =IF(VLOOKUP(Sheet1!A21,'Docs'!$A$4:$B$93,2,FALSE)="#N/A","0",VLOOKUP(She...

Vlookup Plus...?
Hi, I wish to create a roster tool for managers (who are very basic Excel users) to plan the shifts of workers. There are a selection of standard shifts but non-standard shifts are often needed. The required shifts change each pay period. Sheet 2 is called Shifts. Row 1 columns A to L is as follows Shift StartTime FinishTime MealBreak HrsPaid Mon Tue Wed Thu Fri Sat Sun Row 2 and following will have the ShiftName in column A and then the detail of the standard shifts across the row. Sheet 1 is called Roster. Row 1 columns A to L is as follows Shift StartTime FinishTime Meal...

Vlookup help #13
I use the Vlookup a lot and sometimes in some very large spreadsheets. As you can imagine I spend a lot of time counting columns to find the column index number. Is there a formula I can use that will do that counting for me? For example if my lookup value is in column A and my return value is in column C the answer for this formula should be 3. Thank you, Shelly -- shelly2 ------------------------------------------------------------------------ shelly2's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29852 View this thread: http://www.excelforum.com/show...

Nested if/and
trying to exceed the if statement max in excel 2000. I don't have the experience necessary to write a function in vba. Can someone please help me. This is what I am trying to accomplish. I have 40 worksheets set up as bonus reviews. The review bonus dollar amounts are based on the number of hours worked and the performance score. Here is a shot in the dark at what I need in the vba module. If $B$7 >1000 AND $C$13 = "Good" Then 200 ElseIf $B$7 >1000 AND $C$13 = "Excel" Then 400 ElseIf $B$7 >1000 AND $C$13 = "Outst" Then 500 Elself $B$7...

Vlookup with VBA
I have to do a vlookup with VBA in one worksheet in which the Table_Array will go from column A11 to L11 down to a row, containing a specific text, "Paid." is that possible Assuming that "paid" is in column L R = Application.Match("paid", Range("L11").resize(1000,0),0) + 10 Set LookupTable = Range("All","L" & R) X = Application.VLOOKUP("Jackson",LookupTable,11,0) On Wed, 13 Oct 2004 09:47:02 -0700, "Jeff" <Jeff@discussions.microsoft.com> wrote: >I have to do a vlookup with VBA in one wor...

Converting Excel 2007 Nested "If" Statement to Excel 2003: Overcom
=IF(E33<=250000,(180000*0%)+(E33-180000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=350000,(250000*0.5%)+(E33-250000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=400000,(350000*0.75%)+(E33-350000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=450000,(400000*1.5%)+(E33-400000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=550000,(450000*2.5%)+(E33-450000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=650000,(550000*3.5%)+(E33-550000)*(VLOO...

VlookUp error #2
Hi Pete Kindly gave me a formulae to check 2 seperate columns for a value: =IF(ISNA(MATCH(A2&"",Gazatteer!A$2:B$65001,0)),IF(ISNA(MATCH (A2&"",Gazatteer!$G3:H$65001, 0)),"not found",VLOOKUP(A2&"",Gazatteer! $G2:H$65001,1,0)),VLOOKUP(A2&"", Gazatteer!A$2:D$65001,1,0)) I get not found. When I use 2 seperate Vlookup, the one works (the data is in either A2:C65000 or G2:F65000) The following works: =VLOOKUP( A2, Gazatteer!A$2:C$65536, 1, 0 ) (A) =VLOOKUP( A2, Gazatteer!F$2:G$65536, 1, 0 ) (B) This requires 2 coloumns, rather...

nested ifs
Hi all, I am unfamiliar with nested IF statements. I want to have a formula evaluate the following: b1=.005, b2 could =-.004 or b2 could =.004 If b1 is > than 0, then I want to add 1 to b1 and multiply it by 1+b2 if b2 is > 0, or by 1 less the absolute value of b2 if b2 is < 0. so: 1.005*1.004....which is (b1+1)*(b2+1) or 1.005*.996....which is (b1+1)*(1-|b2|). Thanks for the help david Submitted via EggHeadCafe - Software Developer Portal of Choice Some Advice to n00bs at .NET! http://www.eggheadcafe.com/tutorials/aspnet/e94a9ddb-3994-40e2-9b6d-f998ffc56b...

Nested If help
Hi, I'm having trouble with my If statement, not sure what i'm doing wrong, can anyone help? This is what i have, you can get the gist of what i want my field to do from it: Census: IIf([Census Req]<>”” And [Census Rec’d]<>””,”Sent “ & [Census Req] & “ Rec’d “ & [Census Rec’d], IIf([Census Req] <>”” And [Census Rec’d] =””,”Sent “ & [Census Req], “Rec’d “ & [Census Rec’d])) Any help would be greatly appreciated!! Do yourself a favor and get rid of the apostrophy in the table field name "Census Rec'd". Change your ...

Preventing broken hyperlinks and vlookups
We are planning a re-configuration of our primary Drive and will be moving most of our folders and subfolders. We commonly use hyperlinks between workbooks. We are an Excel dependent department nad have thousands of files within our primary folders and subfolders. During testing, I find that when the source workbook is moved, the hyperlink to that workbook no longer works and I get: "Cannot open specified file." I need to move the folders and subfolders soon and need to know how to prevent this error from occurring when the users try to use the hyperlinks. Also,...

Combine VLookup and MAX ?
Hi @ll, I need a formula (or VBA code) that could help me to extract the MA value for a specific identifer in a range. For example: I have vendors in column A and sales in column B Vendor/Sales a 50 a 53 a 90 b 110 b 36 a 100 c 500 I need to know the MAX for vendor a = 100, b = 110, c = 500 Please advise, Ayat -- Message posted from http://www.ExcelForum.com Ayato, An easy way to do this is to sort on vendor, and then insert subtotals, function Max. HTH, Nikos "Ayato >&...

Nesting IF or IF
Hi Hoping someone can help. I want advise on how to nest IF or alternative way to get the formula to work. I want a formula that will say if A1 - P2, and outage time is greater then 2 hours, then failed SLA, if less then 2 hours then Met SLA, or If A1 = P1, and outage time is greater than 1 hour then failed SLA, if less then 1 hour them Met SLA. is this posible A1 B1 Priority Outage time P2 00:16 P1 02:20 Give us a list of the conditions and results u want. Example a1-p2 = 4 and time > 2 -...

Excel
Can someone please help me. I have been trying to figure this one out all day. It seems so simple, I guess I'm brain dead. I'm trying to write a formula that will return results for the folowing example: aaa A A B B A bbb C A B B C ccc A A B B B aaa C C A A B bbb C A B B C ccc A A C C A I need a formula that will count the total number of times each of the colum results (A,B and C) appear for e...

Formula returned in vlookup function
I've used vlookup for a while now in the office 2003. Just converted to office 2007. Now when I do a vlookup I get the formula displayed in my current cell instead of a returned value. I can't figure our what I'm doing wrong. Can someone help? Thanks bmc You've probably got a tick in the wrong place: Try Office Button (top left hand corner) Excel Options (bottom line) Click on advanced Scroll down to Display options for this worksheet and untick 'Show formulas in cells instead of their calculated results Click OK Hope this helps "87vette" wrote...