Index match ignoring zero values (help)
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
    38
    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
    38
    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
    26,897
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 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 365 on Win 10

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
  •