Nested IF driving me nuts...

I have a workbook with a few sheets in it.

One has master data:
Status	Item #	Description
Film	     Former	          Pkg Lot Code format
A	        00121	My item
00131	-	          XYZXYZ

Many lines of data. I have another sheet that users are instructed to
enter the item number ( In Cell B1), and it populates a cell below
with VLOOKUP data. There are a number of cells that do this.

This formula displays the Description.

=IF($B1<>"",VLOOKUP($B1,MasterInfo!$C$3:$D$228,2,FALSE),"")

The conundrum is the Status field. What we'd like to do is:
1. Check if a number entered in B1 matches one in the MasterInfo
sheet.
2. If it does, display the description IF the Status is A. If the
status is I, display "Inactive Profile". If the Status is P, display
"Pending Profile".

Anyone that could assist me is greatly appreciated.
0
John
12/8/2009 6:23:49 PM
excel 39879 articles. 2 followers. Follow

6 Replies
554 Views

Similar Articles

[PageSpeed] 24

Try this:

=3DIF($B1<>"",IF(ISNA(MATCH($B1,MasterInfo!$C:$C,0)),"",IF(INDEX
(MasterInfo!$A:$A,MATCH($B1,MasterInfo!$C:$C,0))=3D"I","Inactive
Profile",IF(INDEX(MasterInfo!$A:$A,MATCH($B1,MasterInfo!$C:$C,0))
=3D"P","Pending Profile",VLOOKUP($B1,MasterInfo!$C$3:$D$228,2,0)))))

It doesn't test specifically for a status of A - I've assumed that the
status can only be I, P or A.

Hope this helps.

Pete

On Dec 8, 6:23=A0pm, John Croson <pcn...@gmail.com> wrote:
> I have a workbook with a few sheets in it.
>
> One has master data:
> Status =A0Item # =A0Description
> Film =A0 =A0 =A0 =A0 Former =A0 =A0 =A0 =A0 =A0 =A0 =A0 Pkg Lot Code form=
at
> A =A0 =A0 =A0 =A0 =A0 =A0 =A0 00121 =A0 My item
> 00131 =A0 - =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 XYZXYZ
>
> Many lines of data. I have another sheet that users are instructed to
> enter the item number ( In Cell B1), and it populates a cell below
> with VLOOKUP data. There are a number of cells that do this.
>
> This formula displays the Description.
>
> =3DIF($B1<>"",VLOOKUP($B1,MasterInfo!$C$3:$D$228,2,FALSE),"")
>
> The conundrum is the Status field. What we'd like to do is:
> 1. Check if a number entered in B1 matches one in the MasterInfo
> sheet.
> 2. If it does, display the description IF the Status is A. If the
> status is I, display "Inactive Profile". If the Status is P, display
> "Pending Profile".
>
> Anyone that could assist me is greatly appreciated.

0
pashurst (2576)
12/8/2009 6:52:47 PM
On Dec 8, 12:52=A0pm, Pete_UK <pashu...@auditel.net> wrote:
> Try this:
>
> =3DIF($B1<>"",IF(ISNA(MATCH($B1,MasterInfo!$C:$C,0)),"",IF(INDEX
> (MasterInfo!$A:$A,MATCH($B1,MasterInfo!$C:$C,0))=3D"I","Inactive
> Profile",IF(INDEX(MasterInfo!$A:$A,MATCH($B1,MasterInfo!$C:$C,0))
> =3D"P","Pending Profile",VLOOKUP($B1,MasterInfo!$C$3:$D$228,2,0)))))
>
> It doesn't test specifically for a status of A - I've assumed that the
> status can only be I, P or A.
>
> Hope this helps.
>
> Pete
>
> On Dec 8, 6:23=A0pm, John Croson <pcn...@gmail.com> wrote:> I have a work=
book with a few sheets in it.
>
> > One has master data:
> > Status =A0Item # =A0Description
> > Film =A0 =A0 =A0 =A0 Former =A0 =A0 =A0 =A0 =A0 =A0 =A0 Pkg Lot Code fo=
rmat
> > A =A0 =A0 =A0 =A0 =A0 =A0 =A0 00121 =A0 My item
> > 00131 =A0 - =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 XYZXYZ
>
> > Many lines of data. I have another sheet that users are instructed to
> > enter the item number ( In Cell B1), and it populates a cell below
> > with VLOOKUP data. There are a number of cells that do this.
>
> > This formula displays the Description.
>
> > =3DIF($B1<>"",VLOOKUP($B1,MasterInfo!$C$3:$D$228,2,FALSE),"")
>
> > The conundrum is the Status field. What we'd like to do is:
> > 1. Check if a number entered in B1 matches one in the MasterInfo
> > sheet.
> > 2. If it does, display the description IF the Status is A. If the
> > status is I, display "Inactive Profile". If the Status is P, display
> > "Pending Profile".
>
> > Anyone that could assist me is greatly appreciated.

Thanks for trying this, but it still only prints the description,
regardless of the status. I'll try mucking about with it today.
0
pcnorb (1)
12/9/2009 3:29:18 PM
Just check that you do not have any space characters in there along
with the status letter. Use =3DLEN( ) to find out how many characters
you have in the status cells.

Hope this helps.

Pete

On Dec 9, 3:29=A0pm, John Croson <pcn...@gmail.com> wrote:
> Thanks for trying this, but it still only prints the description,
> regardless of the status. I'll try mucking about with it today.
0
Pete_UK
12/9/2009 3:41:20 PM
On Dec 9 2009, 9:41=A0am, Pete_UK <pashu...@auditel.net> wrote:
> Just check that you do not have any space characters in there along
> with the status letter. Use =3DLEN( ) to find out how many characters
> you have in the status cells.
>
> Hope this helps.
>
> Pete
>
> On Dec 9, 3:29=A0pm, John Croson <pcn...@gmail.com> wrote:
>
>
>
> > Thanks for trying this, but it still only prints the description,
> > regardless of the status. I'll try mucking about with it today.

Thanks, but no change. LEN =3D 1.

Any other ideas?
0
John
1/19/2010 4:28:48 PM
On Dec 9 2009, 9:41=A0am, Pete_UK <pashu...@auditel.net> wrote:
> Just check that you do not have any space characters in there along
> with the status letter. Use =3DLEN( ) to find out how many characters
> you have in the status cells.
>
> Hope this helps.
>
> Pete
>
> On Dec 9, 3:29=A0pm, John Croson <pcn...@gmail.com> wrote:
>
>
>
> > Thanks for trying this, but it still only prints the description,
> > regardless of the status. I'll try mucking about with it today.

IT WORKED. I just screwed something up prior to my last post.

THANKS FOR THE HELP!!!!!!!
0
John
1/19/2010 4:33:50 PM
You're welcome, John - thanks for feeding back (6 weeks later !!)

Pete

On Jan 19, 4:33=A0pm, John Croson <pcn...@gmail.com> wrote:
> On Dec 9 2009, 9:41=A0am, Pete_UK <pashu...@auditel.net> wrote:
>
> > Just check that you do not have any space characters in there along
> > with the status letter. Use =3DLEN( ) to find out how many characters
> > you have in the status cells.
>
> > Hope this helps.
>
> > Pete
>
> > On Dec 9, 3:29=A0pm, John Croson <pcn...@gmail.com> wrote:
>
> > > Thanks for trying this, but it still only prints the description,
> > > regardless of the status. I'll try mucking about with it today.
>
> IT WORKED. I just screwed something up prior to my last post.
>
> THANKS FOR THE HELP!!!!!!!

0
Pete_UK
1/20/2010 12:55:17 AM
Reply:

Similar Artilces:

Question regarding the simultaneous use of tape drives
Hi folks, a customer of us wants to use DPM instead of their current Symantec solution. A question I coudn't answer was if they could use their 6-drive tape-robot to for example backing up and restoring at the same time. Thank you in advance for your support! -- Christoph Schmidt || IT Consultant @ TOP TECHNOLOGIES CONSULTING || MCITP EA If there 6 drives available in a library 6 tape related jobs can be run in parallel at any time. Thanks, Praveen D [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights. "Christop...

hard drives not recognized when installing XP Pro
i built my own computer and all went well at first. i installed OEM XP Pro 64 bit on the RAID 0 configured hard drives (2). everything worked great, but i could not find drivers for my 32 bit hardware such as Linksys wirelass cards so i decided to buy the 32 bit version of XP Pro. i tried installing the 32 bit version of XP Pro but now the Windows setup stops and reports that it cannot continue because it cannot detect a hard drive. i cant boot up to the 64 bit version that was there either help ...

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

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

Driving Me Nuts
Hi: Posted earlier about an issue with a leftover public folder hierarchy from 5.5 migration and now this is driving me nuts. I just want to get rid of the entire PF tree but cannot set permissions on any folders or delete them. Shows owner as blank. I fear deleting the entire PF store because of the free/busy used for calendaring. What can I do to whack these things already. Thanks Hi, Download pfdavadmin (from Microsoft) and set the desired permissions from this program. Leif "Joe" <a@a.com> wrote in message news:OU7E$SKnGHA.4788@TK2MSFTNGP02.phx.gbl... > Hi...

Can't Stop External Hard Drive
I have a Western Digital 250 gb external hard drive connected via USB port. I am also running Outlook 2007, which I believe has a function searchindexer.exe that searches all my drives to index the files on them. I want to be able to unplug the drive without first turning off my computer. But I get messages saying that the drive can't be stopped. It used to be that if I stopped searchindexer.exe, I could stop the drive. But that's stopped working. Here are two questions: 1. What is preventing me from stopping the drive?; 2. What is the worst that can happe...

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

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

Save files from public folder in Outlook to Folder on public drive
I have an access database used to keep track of documents recieved into outlook public folder. I want the access form where users enter the data to be viewed in Outlook. Also after viewing the document and filling in the various criteria including the filename, when post is selected on form I want file to close/ data to be updated to access database table/ and file to be saved into a folder sitting on public server.. any ideas - Thanks ...

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

Control of tape drive/library from DPM 2007
I would like to be able to specify a particular drive/tape slot for a protection group. We have a 4-drive / 48-slot tape library from DEll. Justification: I need to backup all BMR server images to a single tape (or 2, 3 if necessary) and send the tapes to an off-site storage. Currently, the disk-to-tape data is sent to all 4 tape drives with whatever tape available on the tape library. Your help is greatly appreciated. Bill Dear Bill, as far as I understand it, this behaviour is by design. DPM philosophy is protection group and replica orientated, not classical tap...

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

Error messages after renaming my (C) Drive
After renaming the (C) drive I found that the previous name did not change in networked items. So I went back in and pressed okay to a statement that said I would own the drive. I thought it had to do with renaming and pressed okay. Now I am getting this message all the time (listed below). The windows updates could not up date the computer for the same reason. Is there a way to correct the problem. I don't even know how to get the box that I did this for and what to change it back to anyway. Okay Lynn here it is, straight from the Urban Dictionary: iDiane: Someone am...

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

Can I run ON from an external drive without it being installed on
Can I run ON from an external drive without it being installed on a local machine? "Ang130" <Ang130@discussions.microsoft.com> wrote in message news:5982603B-D3AC-48F3-84A4-D96DB4696889@microsoft.com... > Can I run ON from an external drive without it being installed on a local > machine? With Ceedo software Ang130 wrote: > Can I run ON from an external drive without it being installed on a > local machine? YES, if run under Ceedo with the Argo installer.[1] In theory it should also work with a USB stick. It would have a superfast stick, m...

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

Citizen Women's Eco-Drive Riva Diamond Accented Watch #EW0890-58D
Price:$395.00 Image: http://bestdeallocator.info/image.php?id=B000ZPQSBK Best deal: http://bestdeallocator.info/index.php?id=B000ZPQSBK I bought this to give as a gift nearly a year ago and it is still working great. It's received no special attention as far as charging or general care goes and it is still keeping great time. i hate to say this because it is a beautiful watch--but the diamonds fell out...had to return it...luckily i bought it at Macys and bought the warranty...it was over 30 days and they refunded my money after i complained that they wanted to send it back to the ...

From share drive particular excel file takes long time to open
Hi All, In our environment we have shared folders and files. Excel file is shared and able to open by all who have been given access without any issues. But particular for one user in her system the excel file open is getting delayed. It is taking 15-20min and after that it is getting locked. But at the same time it we open in another end without any issue the file is getting opened in within 5min. Tired the following steps: Reparied the excel Re-installed office Renamed the excel file and created a new one and checked. Still it is taking time to open Copied the excel fi...

How can you copy Clip Art from web to hard drive
Instead of going to the Internet, could I just copy the Clip Art to my hard drive for convenience? I do a lot of Power Point presentations. I have Office 2003. Yes you can copy or download pictures from the internet (check copyrights first) to a folder (i.e. pictures) on your hardrive. You then have to insert the picture in PP as Insert->from File. (Note: this is not the PP forum). "jodawn" wrote: > Instead of going to the Internet, could I just copy the Clip Art to my hard > drive for convenience? I do a lot of Power Point presentations. I have > ...

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

Problems saving to network drive (Airport Extreme)
Got one of the new airport extreme base stations so I could attach an external drive and share it across multiple computers on the same LAN. However, I have discovered a problem. Any time I try to save a file directly to the drive, I get an error message that it cannot access the file. The only way I can get it on the drive is to save it to the computer, then drag it to the shared drive. I'd rather not do that. I have already tried every variation of security protocols for the shared drive (login with special drive password, login with computer password, or login with username and pas...

Finding Records on a shared drive
I have split my database with the tables on a remote shared drive. It's a fairly simple customer tracking application that I want to update periodically from an Excel Spreadsheet. I have set up a Dowhile/loop within the updating procedure that steps through each row in xls and opens a recordset where the custId equals the selected cell. I then update the customer table from the spreadsheet. Splitting the database has really slowed things down. Would it be faster to open a recordset with no WHERE clause outside the loop and use the findfirst method within the loop to locate records?...

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

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