+ Reply to Thread
Results 1 to 8 of 8

Index/match "if not" criteria

  1. #1
    Forum Contributor
    Join Date
    10-26-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2010
    Posts
    138

    Index/match "if not" criteria

    Hi I need additional criteria for this formula

    =IFERROR(INDEX(Committee!$D$2:$D$38;MATCH($B3&"^"&P$2&"^"&$D3;INDEX(Committee!$A$2:$A$38&"^"&Committee!$B$2:$B$38&"^"&Committee!$C$2:$C$38;0);0));"")

    which is if cell does not match Committee!$A$2:$A$38&"^"&Committee!$B$2:$B$38&"^"&Committee!$C$2:$C$38 to return to x. Please see attached document. Thank you.
    Attached Files Attached Files
    Last edited by jewellove; 12-27-2012 at 10:42 AM.

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Index/match "if not" criteria

    just put "x" instead of "" at the end of the iferror part
    "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

  3. #3
    Forum Contributor
    Join Date
    10-26-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2010
    Posts
    138

    Re: Index/match "if not" criteria

    Martin, I inserted your code. It put X to all cells that shouldn't.. Kindly see the attachment.. Thanks


    Quote Originally Posted by martindwilson View Post
    just put "x" instead of "" at the end of the iferror part
    Attached Files Attached Files

  4. #4
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,138

    Re: Index/match "if not" criteria

    only Q7? how about Q12, Q17 & Q18? if my assumption is right, then maybe a simple:
    =IF(COUNTIF(M3:P3,">""")=0,"x","")

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  5. #5
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Index/match "if not" criteria

    cant you just use
    =IF(AND(o3=""",P3="",Q3=""),"x","")
    Last edited by martindwilson; 12-27-2012 at 10:56 AM.

  6. #6
    Forum Contributor
    Join Date
    10-26-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2010
    Posts
    138

    Re: Index/match "if not" criteria

    Beni,

    It is for the whole column Q. The searched cell is in Committee Tab Column B. If you notice it is a list of committee (Audit, Nomination, Risk and etc,).
    In Board Tab Column M to P look okay.. I need to put X if the person is neither in audit, nomination and risk committee...

    I wish you a pleasant evening


    Quote Originally Posted by benishiryo View Post
    only Q7? how about Q12, Q17 & Q18? if my assumption is right, then maybe a simple:
    =IF(COUNTIF(M3:P3,">""")=0,"x","")

  7. #7
    Forum Contributor
    Join Date
    10-26-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2010
    Posts
    138

    Re: Index/match "if not" criteria

    Martin,

    The information for columm Q has to be lifted from Committee Tab Column B..

    Thanks & Regards,


    Quote Originally Posted by martindwilson View Post
    cant you just use
    =IF(AND(o3=""",P3="",Q3=""),"x","")

  8. #8
    Forum Moderator 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
    28,752

    Re: Index/match "if not" criteria

    @jewellove

    Your post does not comply with Rule 4 of our Forum RULES, , and with 49 posts you really should know them.. Don't Private Message, Visitor message or email questions to moderators or other members. All questions and answers will benefit other posters like yourself. The point of having a public forum is to share solutions to common (and sometimes uncommon) problems with all members.
    Last edited by Richard Buttrey; 12-27-2012 at 12:50 PM.
    Richard Buttrey

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

+ 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