SOLVED
Home

Index and match 2 values to then create a percentage

%3CLINGO-SUB%20id%3D%22lingo-sub-312161%22%20slang%3D%22en-US%22%3EIndex%20and%20match%202%20values%20to%20then%20create%20a%20percentage%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-312161%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20am%20looking%20to%20make%20a%20table%20of%20stock%20market%20index%20performance%20for%20various%20time%20ranges.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20tried%20using%20the%20index%20and%20match%20function%20but%20not%20sure%20how%20to%20incorporate%20that%20into%20a%20wider%20capture%20all%20function.%3CBR%20%2F%3E%3CBR%20%2F%3EEffectively%2C%20i%20want%20to%20do%202%20index%20and%20match%20functions%20then%20do%20some%20simple%20maths%20on%20those%202%20results.%3CBR%20%2F%3E%3CBR%20%2F%3EFormula%20looks%20as%20follows%3A%20%3DINDEX('Index%20Raw%20Data'!C4%3AC59%2CMATCH(Sheet1!I2%2C'Index%20Raw%20Data'!B4%3AB59%2C0))%20%3CSTRONG%3Eminus%20this%20function%3C%2FSTRONG%3E%26nbsp%3B%3DINDEX('Index%20Raw%20Data'!C5%3AC60%2CMATCH(Sheet1!I3%2C'Index%20Raw%20Data'!B5%3AB60%2C0))%20%3CSTRONG%3Edivided%20by%20the%20result%20of%20the%20second%20function%3C%2FSTRONG%3E%26nbsp%3Bto%20get%20the%20percentage%20change.%3CBR%20%2F%3E%3CBR%20%2F%3ESample%20workbook%20attached%20using%20cells%20I2%20and%20I3%20as%20example%20date%20ranges%20for%20the%20index%20and%20match%20function.%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20information%20is%20pulled%20from%20the%20%22index%20Raw%20Data%22%20tab.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%3CBR%20%2F%3EJoel%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-312161%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-312796%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20and%20match%202%20values%20to%20then%20create%20a%20percentage%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-312796%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20so%20much!%20this%20worked%20a%20treat!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3CP%3EJoel%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-312251%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20and%20match%202%20values%20to%20then%20create%20a%20percentage%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-312251%22%20slang%3D%22en-US%22%3E%3CP%3EOr%20to%20make%20it%20bit%20more%20flexible%3C%2FP%3E%0A%3CPRE%3E%3DINDEX(%0A%20%20%20OFFSET('Index%20Raw%20Data'!%24A%241%2C3%2C%0A%20%20%20%20%20%20MATCH(%24A4%2C'Index%20Raw%20Data'!%241%3A%241%2C0)%2B(MATCH(%24A4%2C'Index%20Raw%20Data'!%241%3A%241%2C0)%26gt%3B2)%2C%0A%20%20%20%20%20%20COUNTA('Index%20Raw%20Data'!%24B%3A%24B))%2C%0A%20%20%20MATCH(I%242%2C%0A%20%20%20%20%20%20OFFSET('Index%20Raw%20Data'!%24A%241%2C3%2C%0A%20%20%20%20%20%20%20%20%20MATCH(%24A4%2C'Index%20Raw%20Data'!%241%3A%241%2C0)-(MATCH(%24A4%2C'Index%20Raw%20Data'!%241%3A%241%2C0)%3D2)%2C%0A%20%20%20%20%20%20%20%20%20COUNTA('Index%20Raw%20Data'!%24B%3A%24B))%2C0))%20%2F%0A%20INDEX(%0A%20%20%20OFFSET('Index%20Raw%20Data'!%24A%241%2C3%2C%0A%20%20%20%20%20%20MATCH(%24A4%2C'Index%20Raw%20Data'!%241%3A%241%2C0)%2B(MATCH(%24A4%2C'Index%20Raw%20Data'!%241%3A%241%2C0)%26gt%3B2)%2C%0A%20%20%20%20%20%20%20%20%20COUNTA('Index%20Raw%20Data'!%24B%3A%24B))%2C%0A%20%20%20%20%20%20MATCH(I%243%2C%0A%20%20%20%20%20%20%20%20%20OFFSET('Index%20Raw%20Data'!%24A%241%2C3%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20MATCH(%24A4%2C'Index%20Raw%20Data'!%241%3A%241%2C0)-(MATCH(%24A4%2C'Index%20Raw%20Data'!%241%3A%241%2C0)%3D2)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20COUNTA('Index%20Raw%20Data'!%24B%3A%24B))%2C0))%0A%20%20-1%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-312195%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20and%20match%202%20values%20to%20then%20create%20a%20percentage%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-312195%22%20slang%3D%22en-US%22%3EHi%20%3CBR%20%2F%3E%3CBR%20%2F%3ETry%20this%3CBR%20%2F%3E%3CBR%20%2F%3E%3D1-%20(%20INDEX('Index%20Raw%20Data'!C5%3AC60%2CMATCH(I3%2C'Index%20Raw%20Data'!B5%3AB60%2C0))%20%20%20%20%2F%20%20INDEX('Index%20Raw%20Data'!C4%3AC59%2CMATCH(I2%2C'Index%20Raw%20Data'!B4%3AB59%2C0))%20%20)%3C%2FLINGO-BODY%3E
Caesarus7
New Contributor

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

3 Replies
Solution
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)) )

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

 

Thank you so much! this worked a treat!

 

Cheers

Joel 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies