Jan 08 2019 08:38 PM - edited Jan 08 2019 08:44 PM
Hi all,
I am looking to make a table of stock market index performance for various time ranges.
I have tried using the index and match function but not sure how to incorporate that into a wider capture all function.
Effectively, i want to do 2 index and match functions then do some simple maths on those 2 results.
Formula looks as follows: =INDEX('Index Raw Data'!C4:C59,MATCH(Sheet1!I2,'Index Raw Data'!B4:B59,0)) minus this function =INDEX('Index Raw Data'!C5:C60,MATCH(Sheet1!I3,'Index Raw Data'!B5:B60,0)) divided by the result of the second function to get the percentage change.
Sample workbook attached using cells I2 and I3 as example date ranges for the index and match function.
The information is pulled from the "index Raw Data" tab.
Thanks
Joel
Jan 08 2019 11:42 PM
SolutionJan 09 2019 01:35 AM
Or to make it bit more flexible
=INDEX( OFFSET('Index Raw Data'!$A$1,3, MATCH($A4,'Index Raw Data'!$1:$1,0)+(MATCH($A4,'Index Raw Data'!$1:$1,0)>2), COUNTA('Index Raw Data'!$B:$B)), MATCH(I$2, OFFSET('Index Raw Data'!$A$1,3, MATCH($A4,'Index Raw Data'!$1:$1,0)-(MATCH($A4,'Index Raw Data'!$1:$1,0)=2), COUNTA('Index Raw Data'!$B:$B)),0)) / INDEX( OFFSET('Index Raw Data'!$A$1,3, MATCH($A4,'Index Raw Data'!$1:$1,0)+(MATCH($A4,'Index Raw Data'!$1:$1,0)>2), COUNTA('Index Raw Data'!$B:$B)), MATCH(I$3, OFFSET('Index Raw Data'!$A$1,3, MATCH($A4,'Index Raw Data'!$1:$1,0)-(MATCH($A4,'Index Raw Data'!$1:$1,0)=2), COUNTA('Index Raw Data'!$B:$B)),0)) -1
Jan 09 2019 04:53 PM
Thank you so much! this worked a treat!
Cheers
Joel
Jan 08 2019 11:42 PM
Solution