Results 1 to 5 of 5

Thread: Index match ignoring zero values (help)
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2009
    Location
    Gulf of Mexico
    Posts
    602
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Index match ignoring zero values (help)

    How can I make this formula ignore blanks and zero values?
    Code:
    =INDEX(O9:BV9,MATCH(MIN(O10:BV10),O10:BV10,0))
    Thanks,
    Pujo

  2. #2
    New Member
    Join Date
    Mar 2018
    Location
    Katy, TX
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index match ignoring zero values (help)

    Hi Pujo

    In place of MIN(O10:BV10), try AGGREGATE(15,7,(O10:BV10)/((O10:BV10<>0)*(O10:BV10<>"")),1)

    Hope that helps.

  3. #3
    Board Regular
    Join Date
    Feb 2009
    Location
    Gulf of Mexico
    Posts
    602
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index match ignoring zero values (help)

    Thanks Elliott however, this does not work for me. I am still using Excel 2010 (i know)...
    I get errors in the formula.

  4. #4
    New Member
    Join Date
    Mar 2018
    Location
    Katy, TX
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index match ignoring zero values (help)

    Ok, try the following

    MIN(IF(O10:BV10<>0,IF(O10:BV10<>"",O10:BV10)))

    Confirm by pressing Ctrl + Shift + Enter.

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,093
    Post Thanks / Like
    Mentioned
    390 Post(s)
    Tagged
    41 Thread(s)

    Default Re: Index match ignoring zero values (help)

    The Aggregate formula should work for 2010

    Excel 2013/2016
    NOPQRSTUV
    9OPQRSTUV
    10T110756

    Data



    Worksheet Formulas
    CellFormula
    N10=INDEX(O9:BV9,MATCH(AGGREGATE(15,7,(O10:BV10)/((O10:BV10<>0)*(O10:BV10<>"")),1),O10:BV10,0))

    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •