1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How do i skip an index that returns false

Discussion in 'Ask an Excel Question' started by calum milton, Jan 12, 2018.

  1. calum milton

    calum milton New Member

    Messages:
    8
    =INDEX(IF(ConcessionTracker!M3:M23="CONTESTED",ConcessionTracker!H3:H23),"")

    I have came up with this and it returns a list containing what i need but also FALSE for every one that doesn't meet the criteria. All i want is for it to return the value in the h for just the ones that have "contested" in the m column
  2. vletm

    vletm Excel Ninja

    Messages:
    3,458
    calum milton
    1) Hi!
    2) Syntaxes for
    INDEX(reference,row_num,column_num,area_num)
    INDEX(array,row_num,column_num)

    3) Syntax for
    IF(logical_test,value_if_true,value_if_false)
    4) You ask ... something like ...
    Index(IF(logical_test,value_if_true,value_if_false),row_num,column_num)
    which won't work!
    The 1st part of index have to be reference or array.
    5) Did You get any idea?
  3. calum milton

    calum milton New Member

    Messages:
    8
    when i used what i had it returned

    Q27058107713
    FALSE
    FALSE
    FALSE
    FALSE
    Q27487107112
    FALSE
    FALSE
    FALSE
    FALSE
    FALSE
    Q11058245613
    FALSE
    FALSE
    FALSE
    FALSE
    FALSE
    Q27497857413
    FALSE
    FALSE
    FALSE.

    what i wanted was for it to just return

    Q27058107713
    Q27487107112
    Q11058245613
    Q27497857413

    without all the FALSE returns
  4. vletm

    vletm Excel Ninja

    Messages:
    3,458
    calum milton
    Did You try to read my message?
    Excel works with Excel rules!
    You should follow those Excel rules while You're using Excel.
    Index(IF(logical_test,value_if_true,value_if_false),row_num,column_num)
    As written, Your red part cannot work.
  5. calum milton

    calum milton New Member

    Messages:
    8
    Ignoring the if statement, what function would i have to use to in order for an INDEX to not display the rows that don't contain the specific information? also thanks for the quick reply
  6. vletm

    vletm Excel Ninja

    Messages:
    3,458
    I would do it ex this way ... without any formula

    Attached Files:

  7. calum milton

    calum milton New Member

    Messages:
    8
    using filter? would that still work when the array you are getting it from is 20 down and the place you are putting it is only 10 down?

    i ask this as when i try it i get an error saying it will override data in a cell, also if i give it enough room it then removes the entire row, there is tables at either side of this which gets deleted, i was wanting it to remove the rows just in the array i was bringing in before it gets displayed on the different spreadsheet if that makes any sense
    Last edited: Jan 12, 2018
  8. vletm

    vletm Excel Ninja

    Messages:
    3,458
    calum milton
    ... interesting sentences
    It seems that You're thinking something and You're asking something ... else.
    ... without a sample file from Your, someone else would only guess.
    Your sample file should 'show/tell' as much details as You would need to solve Your case (=How do i skip an index that returns false).
  9. calum milton

    calum milton New Member

    Messages:
    8
    i made a rough copy of the tables i have, i want the Q numbers and the reasons of only the contested concessions moved from table1 to table2

    Attached Files:

  10. calum milton

    calum milton New Member

    Messages:
    8
    this has changed to a completely different question now. my original question was based of what i had which was wrong. now i am basically asking how you would do it
  11. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,465
    Hi ,

    See if this is what you wanted.

    Narayan

    Attached Files:

  12. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,596
    1] Another option

    2] In C4, click for select of Action Required

    3] see attachment

    Regards
    Bosco

    Attached Files:

  13. calum milton

    calum milton New Member

    Messages:
    8
    Thanks guys, that's exactly what i was wanting! :DD
  14. calum milton

    calum milton New Member

    Messages:
    8
    =IFERROR(INDEX(ConcessionTracker!$H$3:$H$23, SMALL(IF(ConcessionTracker!$M$3:$M$23 = "CONTESTED", ROW(ConcessionTracker!$M$3:$M$23) - MIN(ROW(ConcessionTracker!$M$3:$M$23)) + 1), ROWS($A$1:$A1))), "")

    ok so i changed the table name to ConcessionTracker and changed the points from B5-B25 to H3-H23 and G5-G25 to M3-M23 and it now only displays the 1st value? what is causing this?
  15. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,465
    Hi .

    It has to be entered as an array formula , using CTRL SHIFT ENTER.

    Narayan

Share This Page