Title: Excel: Match and Index based on range

Source: https://stackoverflow.com/questions/64164736/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.

【Answer】

This is a procedural computation. You create a set of continuous ranges, loop through each range to query the original data, sort the result by ranges, and export it to a two-dimensional table.

The first two answers are very close to the correct solution. But both got stuck on the sorting operation because they tried to solve the problem using formula. Yet formulas are not suitable for handling a computation with a long series of operations.

Scripting languages are the right choice.

Besides Excel VBA, Python Pandas and esProc SPL can also handle Excel data conveniently. VBA is designed for programmers, Pandas is complicated to install and deploy, and SPL is the simplest. You can use the following SPL script to accomplish your task:

SPL is good at handling operations on xls or csv files. Find more examples and learn more about SPL in http://www.raqsoft.com/p/script-over-csv-xls.