+ Reply to Thread
Results 1 to 10 of 10

If, and, or... Help!!!!

  1. #1
    Registered User
    Join Date
    09-29-2016
    Location
    Jackson, TN
    MS-Off Ver
    excel 365 and google sheets
    Posts
    3

    Angry If, and, or... Help!!!!

    How to best explain what I am looking for...

    I have 3 columns. One column is what we charge our clients for hosting, so it is one set variable. The next column is where I want my formula to display, this is the Hosting level package. It needs to return either Level 1, Level 2, or Level 3... depending on the amount being charged in the first Column. The third column describes the level packages. One cell shows the price of level one, the cell below shows the price for level two, and the next cell below shows the price for level 2.

    The formula i have been trying to use is the IF statement. such as =IF(C2=O2, "Level 1", "")... but as you can see this will only return one value out of one, when I need one value of a possible 3 returned. I have googled this for many hours trying to find the solution. I have come across if statements using AND and OR but that doesn't seem to work either. I don't know if I am just entering it in incorrectly or if I am way off base. Maybe one of you could help me? PLEASE

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: If, and, or... Help!!!!

    it depends, I can't tell from what you wrote if you need an IF / AND, IF / OR or multiple levels of IF, like =IF(c2=o2,"level 1",IF(c2>O2,"level 2","Level 3")) for example.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: If, and, or... Help!!!!

    I would put your levels and amount in a small table, side-by-side, in say, C and D.

    You can then use a vlookup...
    =vlookup(A1,$C$1:$D$3,2,1)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    09-29-2016
    Location
    Jackson, TN
    MS-Off Ver
    excel 365 and google sheets
    Posts
    3

    Re: If, and, or... Help!!!!

    multiple level of IF is what I think I need. I just cant get it to work.... say cell one is $60. What I need the formula to do is compare that $60 to the three levels ($60, $240, $750) and return the text string of the level package. If that makes any sense

    what i had envisioned was =IF(C2=O2, "Level 1", ""), IF(C2=I8, "Level 2", ""), IF(C2=I13, "Level 3", "")

    but that doesn't work.

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: If, and, or... Help!!!!

    what i had envisioned was =IF(C2=O2, "Level 1", ""), IF(C2=I8, "Level 2", ""), IF(C2=I13, "Level 3", "")
    you don't want it that way because as soon as one is found the process ends OR, if C2 doesn't equal O2, you will simply get the blank and the formula is done. You would want it (if you don't use Ford's recommendation) this way...
    what i had envisioned was =IF(C2=O2,"Level 1",IF(C2=I8,"Level 2",IF(C2=I13,"Level 3", ""))) and this assumes that cannot equal more than one of the items such as O2 and I8 for example.

  6. #6
    Registered User
    Join Date
    09-29-2016
    Location
    Jackson, TN
    MS-Off Ver
    excel 365 and google sheets
    Posts
    3

    Re: If, and, or... Help!!!!

    How would I go about using the VLOOKUP? I am not very experienced with this formula.

  7. #7
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: If, and, or... Help!!!!

    well I don't know your data but this would be a sample (guess)
    Attached Files Attached Files

  8. #8
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: If, and, or... Help!!!!

    That uses a "TRUE" statement in the vlookup since I don't know if your data will match exactly or not. If it does match exactly then you should change the TRUE to FALSE in the vlookup formula.

  9. #9
    Valued Forum Contributor Neil_'s Avatar
    Join Date
    04-19-2013
    Location
    Yorkshire
    MS-Off Ver
    Office 365 Enterprise E3 2013 / 2016
    Posts
    479

    Re: If, and, or... Help!!!!

    If you mean O2, O3, O4 for your level values,

    =CHOOSE(MATCH(C2,O2:O4,1),"Level1","Level2","Level3")
    Last edited by Neil_; 09-29-2016 at 01:55 PM.
    Frob first, tweak later

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: If, and, or... Help!!!!

    Without seeing your data, try this...
    A
    B
    C
    D
    1
    10
    Lev1
    15
    Lev1
    2
    20
    Lev2
    20
    Lev2
    3
    30
    Lev3
    25
    Lev3
    4
    13
    Lev1
    5
    23
    Lev2
    6
    33
    Lev3
    7
    16
    Lev1
    8
    26
    Lev3
    9
    36
    Lev3
    10
    19
    Lev1
    11
    29
    Lev3
    12
    39
    Lev3

    B1=IFERROR(VLOOKUP(A1,$C$1:$D$3,2,1),"Lev1")
    copied down

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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