Excel: Match and Index based on range

Question

I am stumped with the following problem and not sure how to accomplish it in excel. Here is an example of the data:

    A            B
 1 Date        Stock_Return
 2 Jan-95     -5.2%
 3 Feb-95     2.1%
 4 Mar-95     3.7%
 5 Apr-95     6.9%
 6 May-95     6.5%
 7 Jun-95    -5.6%
 8 Jul-95     6.6%
 9 Aug-95     6.2%

What I would like is to have the dates returned which fall within a certain return range and sorted from low to high.

For example:

                    1        2       3      4      5 
   Below -7%        0        0       0      0      0 
  -7% to -5%        Jun-95   Jan-95  0      0      0
  -5% to -3%        0        0       0      0      0
  -3% to 0%         0        0       0      0      0
  0% to 3%          Feb-95   0       0      0      0
  3% to 5%          Mar-95   0       0      0      0
  5% to 7%          Aug-95   May-95  Jul-95 Apr-95 0

I thought Index and Match might make the most sense but when I drag across columns it doesn't work. Any help is very much appreciated.

basic basic · Accepted Answer · 2020-10-02T00:51:05

You can use AGGREGATE function:

=IFERROR(AGGREGATE(14,6,$A$2:$A$9/(($B$2:$B$9>$D2)*($B$2:$B$9<=$E2)),COLUMN(A1)),"0")

Excel: Match and Index based on range

2 Answers