+ Reply to Thread
Results 1 to 4 of 4

How do I get Excel to Ignore 0 or BLANK cells with Small, Index, Match

  1. #1
    Registered User
    Join Date
    02-01-2016
    Location
    Houston, TX
    MS-Off Ver
    Office 365, 13, 10
    Posts
    15

    Question How do I get Excel to Ignore 0 or BLANK cells with Small, Index, Match

    I have a pricing sheet that compares a current Supplier to Potential Supplier's prices. I'm attempting to output lowest 3 prices compared to current pricing to facilitate selecting a supplier for ordering products. In my data entries I won't have prices from all vendors...thus a BLANK or a Zero(0). This is giving me Zero(0) outputs for the low price. Is there a solution to my problem ?....or do I need a different approach ? I know just enough Excel to manage some simple uses of Excel. Thank you in advance for assistance !!!!! (Sample File Attached)

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: How do I get Excel to Ignore 0 or BLANK cells with Small, Index, Match

    You seem to be making life HORRIBLY complicated with all those redundant tables down below.

    E18, copied across and down:
    =AGGREGATE(15,6,E$6:E$15/(E$6:E$15>0),ROWS(E$18:E18))

    and in E21, copied across and down:
    =IFERROR(INDEX($D:$D,AGGREGATE(15,6,ROW($D$6:$D$15)/(E$6:E$15=E18),COUNTIF(E$18:E18,E18))),"")
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    02-01-2016
    Location
    Houston, TX
    MS-Off Ver
    Office 365, 13, 10
    Posts
    15

    Re: How do I get Excel to Ignore 0 or BLANK cells with Small, Index, Match

    Glenn - Thanks for the help - I was able to understand your suggestion and apply it to my needs. Worked out great !!! Thank you again !

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How do I get Excel to Ignore 0 or BLANK cells with Small, Index, Match

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Dave

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] INDEX MATCH SMALL but ignore duplicates
    By pareid in forum Excel Formulas & Functions
    Replies: 27
    Last Post: 07-30-2019, 12:18 PM
  2. INDEX MATCH formula, ignore blank cells and define time frames
    By ChildishAlbino in forum Excel General
    Replies: 2
    Last Post: 02-16-2016, 03:16 PM
  3. Excel index match, don't want blank cells in result
    By sambak2 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 12-20-2014, 05:22 AM
  4. Replies: 0
    Last Post: 11-05-2014, 10:54 AM
  5. Excel Formula: IFERROR, INDEX, SMALL, MATCH
    By Jenn12788 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-12-2014, 10:03 PM
  6. [SOLVED] An INDEX MATCH formula has blank cell - need to ignore or delete
    By rls231 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-26-2013, 11:13 PM
  7. Replies: 4
    Last Post: 07-18-2012, 02:34 PM

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