Unable to use MATCH/INDEX to obtain value above and below in a column

Hello,

I have a worksheet "Returns" with a column of dates, in chronological order, and another 50 columns of values (company returns), representing what the company return was on that date.. Each company has a column of daily returns for a period of many years.

I have another worksheet "Dates" with specific dates for each company and I want to take the company name/ company code and the date and match these in the worksheet "Returns". I want to use the date from "Dates" and obtain the return on that day, returns found in"Returns" worksheet. I call the date for company x listed in "Dates" worksheet "Day 0". I have been able to achieve this by using MATCH/INDEX functions in excel.

However, in addition to this I want to take the returns for the 200 days prior to "Day 0" and the 31 days after "Day 0" and transfer these to a new worksheet, for each of the 50 companies. My sample size is soon to include to several thousand so trying to get a way of speeding things up.

Any comments would be greatly appreciated.

RM

Hi Pepe Le Mokko,

Thanks for your response.

I am unsure if this is achievable using an excel function and therefore thought a would need a macro.
I am not sure if there is a way MATCH/INDEX can be extended or if a macro would be the only solution.

Are you able to advise?

RM

Hi Pepe Le Mokko,



"Date" Worksheet

Company Code Date
12345 28/06/1996
23456 29/03/1997
34567 30/04/1998
456789 18/01/2001



"Returns" Worksheet

Dates Market Return 12345 23456 34567
23/11/1993 0.02 0.05 -0.04 -0.09
24/11/1993 0.04 0.08 0.01 0.02
25/11/1993 0.01 0.01 0.01 0.01
26/11/1993 0.02 0.02 0.05 0.08





"Extract" Worksheet

Company 12345 23456 34567 45678
Day -200
…
Day 0
…
Day 30



Market Return 1 2 3 4
Day -200
…
Day 0
…
Day 30


I want to be able to use the "Date" worksheet, taking the company code and the date and find the value in the "Returns" worksheet which matches the company code and associated date. I then want to take this value and the 200 days before and 30 days after and present this in the "Extract" worksheet. I wish to do this for 50 companies.

In addition to this, I want to find the corresponding market return from the "Returns" worksheet for the same dates as the company. The 231 (200 prior & "day 0" and 30 following) company returns and the 231 market returns should be from the same dates. i.e. for every column of 231 company returns there should be 231 market returns below for the same period.

If this requires additional explanation please let me know.


Thanks again,

RM

Hi,

thanks for your message.

yeah, I was unable to see an attachment icon. How do I go about this?

RM

Thanks for your advice.

I have attached a sample. I was unable to upload what I intended due to file size. I have put notes on the face of the worksheet to hopefully allow someone to make sense of this.


Any assistance would be superb, and let me know if further explanation is needed.


RM

Thanks for bearing with me on this one, appreciate your patience. I have talked through the process below. I have also manually done the first one so it is hopefully possible to follow with an example.

From "Sample" worksheet I look to company code and see 914563. The corresponding date is 28/03/1996. I then open "Returns" tab and find from the date column 28/03/1996. I then go across to the column for company 914563 and select the value. In this example it is 0.000. I then highlight the 30 values below the value for 28/03/1996 and 200 values prior to this. (Total of 231 values.) The 28/03/1996, in this case acts as "Day 0". After I have highlighted these 231 values I copy and paste them into "Extract" worksheet. Under the column designated for the company 914563. After pasting the 231 values in the 0.000 value appears in the row designated as "Day 0".

I then return to "Returns" worksheet and using the same date (28/03/1996) I look down the "Market" column and pick out the value corresponding to this date. Once I have this I take 200 values before and 30 values after. The output I am hoping to achieve is 231 values for the company and 231 values from the Market. This is all to be presented in "Extract" tab with the company returns above the market returns but on the same worksheet.

This would then be 1 example complete. I then wish to repeat this process many times.

Thanks once again,

RM

Thanks very much for all your help with this. The formula you suggested work perfectly for obtaining returns for the first two companies i.e. 914563
910215 and corresponding market returns but when I attempt to extend it out to the third (company "905536(A)"), it doesn't work and instead gives #N/A. Why would this be?

Possibly this will rectify itself once I move onto the full sample but wanted to check.

Thanks again though, thoroughly appreciate what you have done to help.

RM

Hi XOR LX,

I am trying to make your earlier solution into something that I can adapt to slightly different, but similar, spreadsheets. I have put below how I think you have sourced the data for each part of the formula.
I have tried to apply the formula but is unsuccessful.

=INDEX(INDEX(Returns!$A$3:$E$663,,MATCH(B$2,Returns!$2:$2,0)),MATCH(INDEX(Sample!$B$2:$D$4,MATCH(B$2,Sample!$B$2:$B$4,0),3),Returns!$A$3:$A$663,0)+$A3)

B$2: The Company Code column title in Extract worksheet.

$A3: The first day in Extract Worksheet. I.e. -200.

Returns!$A$3:$E$663: Returns worksheet and highlight all cells: the dates, market returns and all company returns, do not include titles.

Sample!$B$2:$D$4: Sample worksheet and highlight all company codes and all announcement dates.

Sample!$B$2:$B$4: Sample worksheet and highlight all dates.

Returns!$A$3:$A$663: Returns worksheet and highlight all dates.

Can you help me how to interpret these terms please? In my attempt to adapt the formula I changed the first term here as all of the rows were one y-coordinate higher in the spreadsheet/ one number less on the y-axis. Am I correct in doing this? Also, how would I interpret terms 2) and 3), do these require any change or should they stay constant?


1. "$2:$2,0"

2. ",0),3)"

3. ",0)"


Kind Regards,

RM.

Hi XOR LX,

Since my last message I have been able to work out what each section is doing and now works perfectly for an increased sample.

Thanks again for all your assistance.

Kind Regards,

RM

For Test.xlsx

Hi XOR,

I have a similar problem to my last query.
I did try to adapt the previous approach but unfortunately has been unsuccessful.

I have attached the data I am working on.
- I start in "Sample" tab. I look to the code and the associated year.
- I take the note the code & corresponding year and find these in the "CapEx" tab. Once I have found the value which corresponds to the code and year I select the 3 values previous and 3 values after this- to give me a total of 7 value. I then go into the "Extract" tab and place these so that they find in the window from -3 to +3, the value at the year indicated in the "Sample" tab should be at position 0.

I have manually done the first one. However, I have several hundred examples like this and would be delighted to find an automated value of doing this.

Is this something you'd be able to help with?

Kind Regards,

RM

I have addressed this to XOR but if anyone else is able to assist, that would be greatly appreciated.

RM