+ Reply to Thread
Results 1 to 7 of 7

Index, Match of If, OR function?

  1. #1
    Registered User
    Join Date
    09-29-2018
    Location
    Windsor, ON
    MS-Off Ver
    2016
    Posts
    12

    Index, Match of If, OR function?

    I have been struggling trying to create a formula to return TEXT based on multiple criteria.

    I have 4 columns for dates titled “Promise Date”, “Delivery Date”, “Cancelled Date” and “Return Date (Columns E, F, G & H). In column “I” (titled “Delivery Status”) I wanted to return text based on:

    If only Promise Date entered then returns “Pending”

    If Promise Date and Delivery Date are entered, then returns “Received”

    If Promise Date and Cancelled Date are entered, then returns “Cancelled"

    If Promise Date, Delivery Date, and Return Date are entered then returns “Returned”

    If Columns E to H do not have any data, then Column I returns a blank cell


    Note: I typed in results I want in Column “I”

    See attached
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    23,676

    Re: Index, Match of If, OR function?

    This should get you started:

    =IF(AND(E2<>"",COUNT(E2:H2)=1),"Pending",IF(AND(E2<>"",G2<>"",COUNT(E2:H2)=2),"Cancelled",""))

    You can nest the other IF statements you need between the second IF statement and the red section at the end. Make sure the number of IF statements is reflected with the same number of closing brackets at the end.
    Last edited by AliGW; 12-30-2018 at 10:27 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.
    Are you new to Power Query and don't know what to do with the code you've been given? Have a look here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    24,301

    Re: Index, Match of If, OR function?

    ...another take on it

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

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

    Forum Rules Updated September 2018. Please read them by clicking here.

  4. #4
    Registered User
    Join Date
    09-29-2018
    Location
    Windsor, ON
    MS-Off Ver
    2016
    Posts
    12

    Re: Index, Match of If, OR function?

    Thank you very much, AliGW!!!!!!!!

    I really appreciate that you gave my a start point and explanation/direction for me to complete the formula. Here is my result:

    =IF(AND(E2<>"",COUNT(E2:H2)=1),"Pending",IF(AND(E2<>"",F2<>"",COUNT(E2:H2)=2),"Received",IF(AND(E2<>"",G2<>"",COUNT(E2:H2)=2),"Cancelled",IF(AND(E2<>"",H2<>"",COUNT(E2:H2)=3),"Returned",""))))

    This Forum and Members are Awesome!

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    23,676

    Re: Index, Match of If, OR function?

    Well done! I always think it feels better, ultimately, when we solve the issue ourselves. I am glad the push was sufficient.

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

  6. #6
    Registered User
    Join Date
    09-29-2018
    Location
    Windsor, ON
    MS-Off Ver
    2016
    Posts
    12

    Re: Index, Match of If, OR function?

    Thank you for the quick reply, Richard!!!

    I tried the formula, but a window pops up stating "You've entered too few arguments for this function"

  7. #7
    Registered User
    Join Date
    09-29-2018
    Location
    Windsor, ON
    MS-Off Ver
    2016
    Posts
    12

    Re: Index, Match of If, OR function?

    Thank again, AliGW.

    Post set to "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