+ Reply to Thread
Results 1 to 4 of 4

Help Requested with Nested Small Function Inside Index/Match Formula

  1. #1
    Registered User
    Join Date
    09-09-2008
    Location
    Troy, MI
    MS-Off Ver
    2010
    Posts
    95

    Help Requested with Nested Small Function Inside Index/Match Formula

    Hello,

    I am having an issue with an index/match formula that nests the SMALL function inside in order to return the nth lowest value from the database into a report. I have attached a sample workbook that provides both the database and the report.

    The issue arises when there are two or more values in the database that are the same (in the attached sample, the values are 74 for the New Course with one occurring on 4/11/15 and the other occurring on 4/25/15), yet occur on different dates. The SMALL function identifies the smallest of these values, along with the corresponding date, yet when the SMALL function searches for the second smallest value, it appears the find the second smallest value (which in this case is the same as the first smallest value) and returns it accordingly.

    When searching for the second smallest date that is associated with the second smallest value (cell J19 in the sample workbook), the SMALL function returns the date associated with the first smallest value rather than the second smallest value. The date that I am expecting in this case would be 4/25/15 instead of 4/11/15.

    Would someone please take a look at my formulas and advise as to what may be missing, or suggest a change to my formulas so that when two or more values are the same, the SMALL function (or other suggested function) can return the proper date associated with the second or third smallest value?
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Help Requested with Nested Small Function Inside Index/Match Formula

    Hello
    Take a look at the attached reply. If you're looking for the 3 smallest scores and their corresponding dates, perhaps this might work for you. It uses Array formulas entered with Ctrl+Shift +Enter.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    09-09-2008
    Location
    Troy, MI
    MS-Off Ver
    2010
    Posts
    95

    Re: Help Requested with Nested Small Function Inside Index/Match Formula

    Thank you DBY. I will take a look at your suggetion and follow up.

  4. #4
    Registered User
    Join Date
    09-09-2008
    Location
    Troy, MI
    MS-Off Ver
    2010
    Posts
    95

    Re: Help Requested with Nested Small Function Inside Index/Match Formula

    DBY, your solution worked brilliantly! Thank you!

+ 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] Putting OR inside a INDEX SMALL formula for multiple criteria, can it be done?
    By whetu in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-22-2014, 06:56 AM
  2. [SOLVED] Exclude Zero's from Min Function nested inside an index+Match... all within CELL funtion
    By clemsoncooz in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-05-2014, 05:16 PM
  3. Choosing Name Range inside Index / Match Function
    By gallow in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-05-2012, 07:08 AM
  4. Excel 2007 : using MATCH inside of an INDEX formula
    By can2c in forum Excel General
    Replies: 2
    Last Post: 03-17-2010, 06:37 AM
  5. Index/Match function inside an array
    By tittiot in forum Excel General
    Replies: 2
    Last Post: 01-20-2010, 09:48 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