+ Reply to Thread
Results 1 to 8 of 8

IF function for multiple values

  1. 01-28-2011, 10:50 AM #1
    n1kk1
    n1kk1 is offline
    Registered User
    Join Date
    06-07-2006
    Posts
    57

    IF function for multiple values

    Hi, I have an IF Function to return a particular value - like below

    =IF(E7="perspex - bolts","L/E","")

    But I need it to also look in column J and return L/E if the criteria = High End, so logically I thought something like this

    =IF(E7="perspex - bolts","L/E","")+=IF(J7="High - End","L/E","")ORJ7="Mid - range","L/E","")

    Obviously it isn;t this

    So I only want it to return the "L/E" if the crriteria in columns E and J = "Perspex-bolts" AND "High-End" or "Mid-range

    What formula would I need to achieve this?

    Thanks
    Reply With QuoteRegister To Reply
  2. 01-28-2011, 10:53 AM #2
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: IF function for multiple values

    Try like this

    =IF(AND(E7="perspex - bolts",OR(J7={"High - End","Mid - range"})),"L/E","")
    Audere est facere
    Reply With QuoteRegister To Reply
  3. 01-28-2011, 10:55 AM #3
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: IF function for multiple values

    =IF(AND(E7="perspex - bolts", OR(J7= {"High - End", "Mid - range"})),"L/E","")
    With ANDS and ORS, the arguments follow the logical separator as shown. Does that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  4. 01-28-2011, 11:04 AM #4
    n1kk1
    n1kk1 is offline
    Registered User
    Join Date
    06-07-2006
    Posts
    57

    Re: IF function for multiple values

    perfect - thankyou!
    Reply With QuoteRegister To Reply
  5. 01-28-2011, 11:05 AM #5
    Blake 7
    Blake 7 is offline
    Valued Forum Contributor Blake 7's Avatar
    Join Date
    10-01-2010
    Location
    Bogota, Colombia
    MS-Off Ver
    Excel 2010 64 bit and Excel 2007,
    Posts
    1,377

    Re: IF function for multiple values

    @Chemist - hi dude, hows it going? hope all well with you.

    If you don't mind, could you explain or point me in the right direction re the use of the curly bracket in the formula you just offered?

    Cheers mate
    Blake 7

    If your question has been answered, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

    If any member's response has helped to solve your problem, please consider using the scales icon on the top right of their post to show your appreciation.

    http://failblog.org/
    Reply With QuoteRegister To Reply
  6. 01-28-2011, 11:08 AM #6
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: IF function for multiple values

    it creates a range for the OR to test against same as putting
    OR(J7= "High - End", J7="Mid - range")
    =OR(B1={1,2,3,4,5})
    saves typing
    =or(b1=1,b1=2,b1=3,b1=4,b1=5)
    Last edited by martindwilson; 01-28-2011 at 11:10 AM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula
    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC
    Reply With QuoteRegister To Reply
  7. 01-28-2011, 11:08 AM #7
    n1kk1
    n1kk1 is offline
    Registered User
    Join Date
    06-07-2006
    Posts
    57

    Re: IF function for multiple values

    it should be above your brackets on right hand side - near return key
    Reply With QuoteRegister To Reply
  8. 01-28-2011, 11:13 AM #8
    Blake 7
    Blake 7 is offline
    Valued Forum Contributor Blake 7's Avatar
    Join Date
    10-01-2010
    Location
    Bogota, Colombia
    MS-Off Ver
    Excel 2010 64 bit and Excel 2007,
    Posts
    1,377

    Re: IF function for multiple values

    ahhhhhhhhhh - i see. nice one. Great, thanks mw.
    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)

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