+ Reply to Thread
Results 1 to 5 of 5

Return multiple Results from an IF statement if more than one condition exists

  1. #1
    Forum Contributor
    Join Date
    08-13-2009
    Location
    Caribbean
    MS-Off Ver
    Excel 2003
    Posts
    129

    Return multiple Results from an IF statement if more than one condition exists

    Good morning,

    What I would like is to have an IF statement (or something else if it is more appropriate) that checks the conditions present in a row and returns a variable for all conditions which are present.

    For example, in the attached spread sheet, in O2. The if Statement has multiple conditions which equate to exceptions. However, there are more than 1 exception (i.e., more than one of the conditions exist) yet the IF formula only returns a single value.

    In Row 2 there are 2 exceptions.

    1) Exception: No Initial Payment Collected on Policy
    2) Exception: Premium Payment Plan Timeline in Excess of Guidelines

    However, the formula only returns the first true statement. I understand that this is not an error but it is also not have I have intended for it to work.

    In summary, what is the best way to pull multiple true conditions and return all variables for which the condition is present?

    Thanks!!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: Return multiple Results from an IF statement if more than one condition exists

    Instead of using =IF(AND, why not just separate each of the exception checks and concatenate the results?

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Return multiple Results from an IF statement if more than one condition exists

    Situations like the, remember you have all the columns you need to do any 1 thing you need in each column. So I would create a series of columns to do the tests individually. Then a final column that looks at the "already done" results of the tests and displays the concatenated results, or the word "NONE". You can of course hide those columns.

    So, break those 4 tests out to 4 columns. (O:R)
    O2: =IF(AND(G3<500,I3="Y"),"Exception: Premium Financing on Premium < 500", "")
    P2: =IF(AND(I2="N",L2>5),"Exception: No Payment Plan Yet Premium Not Fully Collected", "")
    Q2: =IF(H2=0,"Exception: No Initial Payment Collected on Policy", "")
    R2: =IF(OR(AND(G2>=500,G2<=1000,J2>4),AND(G2>=1001,G2<=2500,J2>6),AND(G2>=2501,J2>10)),"Exception: Premium Payment Plan Timeline in Excess of Guidelines", "")


    Then in column S, something like this:

    S2: =IF(ISNUMBER(MATCH("Exception*", O2:R2, 0)), IF(O2<>"", "/ " & O2, "") & IF(P2<>"", "/ " & P2, "") & IF(Q2<>"", "/ " & Q2, "") & IF(R2<>"", "/ " & R2, ""), "NONE")
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Forum Contributor
    Join Date
    08-13-2009
    Location
    Caribbean
    MS-Off Ver
    Excel 2003
    Posts
    129

    Re: Return multiple Results from an IF statement if more than one condition exists

    thank you very much folks. Was hoping to be able to spit this all out into one column as I need to hand this over to a more junior staff and do not want to overwhelm them with information. I guess I can just follow you guidance and hide the columns which are not necessary.

    Cheers! Happy weekend

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Return multiple Results from an IF statement if more than one condition exists

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 4
    Last Post: 11-03-2014, 12:40 PM
  2. Need help with a multiple condition IF statement
    By tlstephe in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-02-2014, 10:08 AM
  3. Need help with Multiple condition IF statement
    By tlstephe in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-01-2014, 08:10 AM
  4. Multiple condition IF statement PLEASE HELP
    By jam320 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-24-2014, 02:59 PM
  5. Multiple Condition If statement
    By cbjhay2006 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-14-2013, 07:40 PM
  6. Replies: 9
    Last Post: 07-03-2013, 07:39 AM
  7. Multiple condition IF statement`
    By floricita in forum Excel General
    Replies: 2
    Last Post: 10-13-2011, 05:41 AM

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