+ Reply to Thread
Results 1 to 8 of 8

If statement trouble

  1. #1
    Registered User
    Join Date
    10-19-2012
    Location
    Hazleton, Pa
    MS-Off Ver
    Excel 2010
    Posts
    17

    If statement trouble

    Hi everyone! Thank you in advance for the help with my question. My issue is i have a IF(OR statement that needs to nest over 7 but all the research i have done on how to fix it has not worked in my case. I am looking to compare shift codes to employee records and provide the description of the shift. Here is an example of my current formula...

    =IF(OR(M3="X530",M3="0594",M3="0319",M3="PK16",M3="0333",M3="PK27",M3="0627",M3="0629",M3="PK05",M3="XIB2",M3="0011",M3="FD40",M3="COB1",M3="COB4",M3="CI27",M3="0345"),"Sun - Wed",IF(OR(M3="COB2",M3="0603",M3="0602",M3="0595",M3="0593"),"M,T,TH,F",IF(OR(M3="X531",M3="0596",M3="0321",M3="0604",M3="0334",M3="PK28",M3="0628",M3="0630",M3="PK06",M3="XIB4",M3="0012",M3="FD41"),"Wed - Sat",IF(OR(M3="COB5"),"M-TH",IF(OR(M3="0600",M3="0601",M3="0598",M3="0599"),"Sat-Sun",IF(OR(M3="0252",M3="AVQ7",M3="AVAO",M3="AVAJ",M3="COB3",M3="AVAL",M3="0373",M3="0109",M3="AVF1",M3="AAAA"),"Mon-Fri",IF(M3="","","Not Mapped")))))))

    The formula is on the last column of the worksheet since i pull a report and paste in the results. Since the company i work for has no method to the madness of shift codes i cannot just change the shift codes to be for example 0001-0050 be a standard S-W code. So i need to have shift codes that are all over the place.

    Thanks,
    Rich

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: If statement trouble

    Hi,

    You'll need to build a table of Shift Codes and descriptions, and then use a VLOOKUP instead of an IF test. e.g.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: If statement trouble

    Set up a 2-column table somewhere (eg in columns X and Y) made up of your codes in column X and what you want them to convert to in column Y, so you will have something like this:

    X530 | Sun - Wed
    0594 | Sun - Wed
    0319 | Sun - Wed
    PK16 | Sun - Wed

    and so on.

    Then you could replace your long formula with this:

    =IF(M3="","",IFERROR(VLOOKUP(M3,X:Y,2,0),"Not Mapped"))

    If you come across new codes, just add then to the bottom of the table - they don't need to be in any particular order.

    Hope this helps.

    Pete

  4. #4
    Registered User
    Join Date
    10-19-2012
    Location
    Hazleton, Pa
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: If statement trouble

    Thank you both!!! I would like to use the If & vlookup option but i am having issues getting it to work. I have attached the file to see you you have advice on what i'm doing wrong. The formula is located in AW and the table in AZ:BA also i have a master tab of shift codes.

    Test.xlsmTest.xlsm

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: If statement trouble

    Your lookup values have to be the same format as the data (i.e. text with leading zeros). See attached file, where I have corrected this for you.

    Hope this helps.

    Pete
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    10-19-2012
    Location
    Hazleton, Pa
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: If statement trouble

    Thank you so much! For knowledge can you let me know what and how this was changed?

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: If statement trouble

    Well, at first I set the format of your "numeric" cells to Text, and then started to manually insert 00 in front of the numeric values (by typing them). But, when I realised that you had so many, I just put this formula in a helper column (eg BB13):

    ="0"&AZ13

    and copied it down for the 3-digit numbers. I then fixed the values and then copied across into column AZ to overwrite the original values that were there.

    Hope this helps.

    Pete

  8. #8
    Registered User
    Join Date
    10-19-2012
    Location
    Hazleton, Pa
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: If statement trouble

    Thank you! Solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1