+ Reply to Thread
Results 1 to 6 of 6

Excel Nesting/IF Formilas

  1. 04-17-2019, 07:41 PM #1
    SP_
    SP_ is offline
    Registered User
    Join Date
    04-17-2019
    Location
    Canada
    MS-Off Ver
    16.3
    Posts
    2

    Excel Nesting/IF Formilas

    Hi guys,

    Still pretty new to the world of excel. So I apologize in advance for any sheer stupidity on my end.

    Basically, I have a list of over 40,000 Project Codes in column A1.

    In column A2, I'd like to have all the Project Names that correlate the the Project Codes in A1. Instead of manually entering this, I thought it would be easier to just have a formula that I could drag down since this database will only continue to grow very quickly.

    It's working for one cell, but the rest of the cells are showing "#NAME?". I just dragged the formula down. So as far as I can tell, the cells changed properly (A2, A3, A4 etc.)

    Can anyone give some feedback on what I'm doing wrong? (Below or attached for a screenshot)

    Below is the code I used in B2 that seemed to work

    =IF(A2="10011336","WED",IF(A2="10011337","CSM",IF(A2="10012245","Facilities",IF(A2="10012512","Facilities",IF(A2="10012514","Facilities",IF(A2="10013801","EDT - ILO",IF(A2="10013804","EDT - IT etc.",IF(A2="10014401","Startup - Geuking",IF(A2="10014403","Startup - Sycuro",IF(A2="10014536","Facilities",IF(A2="10015414","Arrieta Startup",IF(A2="10015899","Facilities",IF(A2="10015900","Facilities",IF(A2="10016375","Facilities",IF(A2="10016568","Philanthropic - IMC",IF(A2="10017047","IMC Cytometry",IF(A2="10017057","IMC Bioinformatics",IF(A2="10017058","IMC Biobanking",IF(A2="10018964","IMC Operating",IF(A2="10019443","Facilities",IF(A2="10021123","EDT Bloom",IF(A2="10020147","Facilities",IF(A2="10019054","IMC Proteomics & Metabolomics",IF(A2="10025278","Philanthropic - Anon",IF(A2="10011128","IICD Projects",IF(A2="10011847","IICD Operations",IF(A2="10014402","Startup - McCoy",IF(A2="10015816","McCoy - Grant",IF(A2="10020147","EDT - Mibi",””)))))))))))))))))))))))))))))
    Attached Images Attached Images
    Reply With QuoteRegister To Reply
  2. 04-17-2019, 08:27 PM #2
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: Excel Nesting/IF Formilas

    a couple things, for those where you are having the output as facilities you could use an OR function instead of multiple IFs.
    BUT, have you considered using a vlookup function and referring to a table of these numbers (that you have in quotes so they must really be text)?
    Also it appears that the last quotes you have at the very end of the formula looks like it isn't really "" but the kind you see when using the quotes in a word document. (here "EDT - Mibi",””)
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci
    Reply With QuoteRegister To Reply
  3. 04-17-2019, 08:30 PM #3
    mehmetcik
    mehmetcik is offline
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Excel Nesting/IF Formilas

    Try this instead:

    =CHOOSE(MATCH(A2,{10011336,10011337,10012245,10012512,10012514,10013801,10013804,10014401,10014403,10014536,10015414,10015899,10015900,10016375,10016568,10017047,10017057,10017058,10018964,10019443,10021123,10020147,10019054,10025278,10011128,10011847,10014402,10015816,10020147},0),"WED","CSM","Facilities","Facilities","Facilities","EDT - ILO","EDT - IT etc.","Startup - Geuking","Startup - Sycuro","Facilities","Arrieta Startup","Facilities","Facilities","Facilities","Philanthropic - IMC","IMC Cytometry","IMC Bioinformatics","IMC Biobanking","IMC Operating","Facilities","EDT Bloom","Facilities","IMC Proteomics & Metabolomics","Philanthropic - Anon","IICD Projects","IICD Operations","Startup - McCoy","McCoy - Grant","EDT - Mibi")
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.
    Reply With QuoteRegister To Reply
  4. 04-17-2019, 08:31 PM #4
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: Excel Nesting/IF Formilas

    to follow up... this and similar sections... IF(A2="10012245","Facilities",IF(A2="10012512","Facilities",IF(A2="10012514","Facilities",...
    instead would be IF(OR(A2="10012245",A2="10012512",A2="10012514"),"Facilities", etc.
    are these "10012245", "10012512" and "10012514" for example, all text? If they are numbers you'll want (need) to remove the quotes around them.

    But I do think a vlookup against a table might be a better route.
    Reply With QuoteRegister To Reply
  5. 04-18-2019, 06:32 AM #5
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: Excel Nesting/IF Formilas

    If you stick with the IF statements, I combined the ones I found that were the same and removed the quotes...
    PHP Code: 
    =IF(A2=10011336,"WED",IF(A2=10011337,"CSM",IF(OR(A2=10012245,A2=10012512,A2=10012514,A2=10014536,A2=10015899,A2=10015900,A2=10016375,A2=10019443,A2=10020147),"Facilities",IF(A2=10013801,"EDT - ILO",IF(A2=10013804,"EDT - IT etc.",IF(A2=10014401,"Startup - Geuking",IF(A2=10014403,"Startup - Sycuro",IF(A2="10015414","Arrieta Startup",IF(A2=10016568,"Philanthropic - IMC",IF(A2=10017047,"IMC Cytometry",IF(A2=10017057,"IMC Bioinformatics",IF(A2=10017058,"IMC Biobanking",IF(A2=10018964,"IMC Operating",IF(A2=10021123,"EDT Bloom",IF(A2=10019054,"IMC Proteomics & Metabolomics",IF(A2=10025278,"Philanthropic - Anon",IF(A2=10011128,"IICD Projects",IF(A2=10011847,"IICD Operations",IF(A2=10014402,"Startup - McCoy",IF(A2=10015816,"McCoy - Grant",IF(A2=10020147,"EDT - Mibi",””))))))))))))))))))))) 
    Reply With QuoteRegister To Reply
  6. 04-18-2019, 01:31 PM #6
    SP_
    SP_ is offline
    Registered User
    Join Date
    04-17-2019
    Location
    Canada
    MS-Off Ver
    16.3
    Posts
    2

    Re: Excel Nesting/IF Formilas

    Wow! You guys are absolutely incredible.

    I never thought to use VLookup and definetly will as I build the rest of this. But looking over your formulas, I definetly see where I went wrong. Thanks for all the help everyone.

    Problem solved!!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Unable to reduce the number of nesting in If OR AND formula with many nesting level
    By varmoh in forum Excel General
    Replies: 4
    Last Post: 02-26-2015, 06:10 AM
  2. Excel Nesting
    By worswick25 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-24-2011, 02:37 PM
  3. [SOLVED] Need formilas value to dynamicaly update on 2 different worksheets in the same spreadsheet
    By CJCoates in forum Excel General
    Replies: 3
    Last Post: 01-26-2006, 07:30 PM
  4. [SOLVED] Help with nesting functions in Excel
    By Chrissi in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 PM
  5. Help with nesting functions in Excel
    By Biff in forum Excel Formulas & Functions
    Replies: 21
    Last Post: 09-06-2005, 02:05 PM
  6. Help with nesting functions in Excel
    By Chrissi in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  7. [SOLVED] Help with nesting functions in Excel
    By Chrissi in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-07-2005, 04:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1