+ Reply to Thread
Results 1 to 5 of 5

How to ignore blank matches with an index and choose the next match (if multiple matches)

  1. #1
    Forum Contributor
    Join Date
    09-17-2009
    Location
    Torquay, England
    MS-Off Ver
    Excel 2013
    Posts
    253

    How to ignore blank matches with an index and choose the next match (if multiple matches)

    Hi

    I have set up an index and there may be multiple matches possible from the results.

    That is fine - as long as the first match isnt a blank. I need it to skip past any blank matches and choose the first completed field that matches.

    Examples attached.

    Thanks
    Attached Files Attached Files

  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: How to ignore blank matches with an index and choose the next match (if multiple matc

    what if the gap is in the middle or last?
    "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
    09-17-2009
    Location
    Torquay, England
    MS-Off Ver
    Excel 2013
    Posts
    253

    Re: How to ignore blank matches with an index and choose the next match (if multiple matc

    Hi,

    I thought that it would only matter if the gap came first? Doesnt the formula select the first match?

    Therefore if the gap was in the middle I assumed it would have already matched a name

  4. #4
    Forum Contributor
    Join Date
    09-17-2009
    Location
    Torquay, England
    MS-Off Ver
    Excel 2013
    Posts
    253

    Re: How to ignore blank matches with an index and choose the next match (if multiple matc

    Is this something that could be addressed with an If statement before the indexing formula? If so, I cant figure it out - any help would be much appreciated.

    Thansk

  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: How to ignore blank matches with an index and choose the next match (if multiple matc

    in d4 of index sheet
    =IF(INDEX('Review Date'!$E$4:$E$10,MATCH(B4,'Review Date'!$C$4:$C$10,0))=0,INDEX('Review Date'!$E$4:$E$10,MATCH(TRUE,INDEX('Review Date'!$E$4:$E$10<>"",0),0),INDEX('Review Date'!$E$4:$E$10,MATCH(B4,'Review Date'!$C$4:$C$10,0)))) but it doesn't seem right to me!

+ 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