Forum Discussion

Caesarus7's avatar
Caesarus7
Copper Contributor
Jan 09, 2019
Solved

Index and match 2 values to then create a percentage

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...
excel
  • Wyn Hopkins's avatar
    Wyn Hopkins
    Jan 09, 2019
    Hi

    Try this

    =1- ( INDEX('Index Raw Data'!C5:C60,MATCH(I3,'Index Raw Data'!B5:B60,0)) / INDEX('Index Raw Data'!C4:C59,MATCH(I2,'Index Raw Data'!B4:B59,0)) )
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 09, 2019

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

 

Resources