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?
Bookmarks