Spreadsheet: 778818d1651338124-dynamic-formula-range-for-sortby-and-index-xmatch-into-a-pivot-table-bloodraven
Sheet: Sheet1

Imagine table 1 is a pivot |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Desired outcome: When the pivot updates with the next year (2022) - to automatically update the ranges for the [NAME] formula so I don't have to drag it to column Q - the same for the INDEX/[NAME] formula on row 21, if possible
PIVOT
Sum of Amount per type of investors and their country of location [Export Currency] | Column Labels
Row Labels | 2007 | 2008 | 2009 | 2010 | 2011 | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 | 2018 | 2019 | 2020 | 2021
A | 0.05444657590220257 | 0.002698520787295552 | 0 | 0 | 0 | 0.1502552456277857 | 0.002592725510416295 | 0.1582239837096263 | 0 | 0 | 0.00211876354964424 | 0.1121541333250347 | 0 | 0.014495696410774 | 0
B | 0.04291086301698705 | 0.06955566874196069 | 0 | 0 | 0.1005165373496684 | 0.006401852570607412 | 0.0006124548449802272 | 0.02297858684579635 | 0 | 0.07124215073745474 | 0.06211112970511097 | 0.0002960510196507307 | 0.0004812566096346063 | 0.0008710846164618434 | 0
C | 0.02225099791437046 | 0.0581955738471419 | 0.08100391290446582 | 0 | 0.01787966634254829 | 0.09604034121121043 | 0.01341276110506697 | 0.01969593158211116 | 0 | 0 | 0.0692511933663281 | 0.002135852847964769 | 0 | 0 | 0
D | 0.01791516100234117 | 0.02761063889388933 | 0.003704850639211112 | 0.02965673204456709 | 0.01533279360648877 | 0.05440319419806378 | 0.01563791163268764 | 0.02285271014705507 | 0.1855305975482003 | 0.03535313606942367 | 0.01680729768029776 | 0.01218787752714034 | 0.03825873357457717 | 0.005268182084978879 | 0.01475902318729185
Other sources | 0.1306916713254331 | 0.2416300315564304 | 0.09669453755233963 | 0.0713828130059257 | 0.1498755527082531 | 0.1647889572813165 | 0.1256757341899426 | 0.1439549378094395 | 0 | 0.2151424230555102 | 0.2366042627440177 | 0.1408687707512306 | 0.0596142421630029 | 0.1073144141499592 | 0.01623755528420053
H | 0.06463977047750077 | 0.1763804207353445 | 0.009791390975057941 | 0.006353070357527387 | 0.1489265656877632 | 0.08100226399633262 | 0.04899638759841817 | 0.1078483560331133 | 0.07964993493884992 | 0.1610183778944911 | 0.03738104262586624 | 0.007769356916436146 | 0.03875444560904738 | 0.02111645003923817 | 0.02156125986892771
A | 0.06332708590780384 | 0.1186631666732476 | 0.119878381397331 | 0.02798206269832287 | 0.1432482181326762 | 0.03600100622059228 | 0.03062274224901136 | 0.05438453102542535 | 0.0516439900732543 | 0 | 0.08061679797875285 | 0.085926988777939 | 0.1098058874924019 | 0.03901803598905765 | 0.04121025160008022
RWE | 0.07720003615550977 | 0.1383946908799795 | 0.06785582139740721 | 0.04557214409643007 | 0.0628314287085647 | 0.03750493946860104 | 0.03399124389640261 | 0.008671619712003462 | 0.1715677070181459 | 0.1042177000554847 | 0.03284518151156756 | 0.09802564923295165 | 0.1334654071577002 | 0.1778159369911378 | 0.06183103713450577
SDFS | 0.03070389097681287 | 0.06722842375781528 | 0.01055215559060109 | 0.007618430653995627 | 0.0933423943096649 | 0.00397919071545598 | 0.07604647658504488 | 0.001125294224391284 | 0.06302745600060514 | 0.00249129049824536 | 0.003144245107672052 | 0.04236525803755952 | 0.1786391645205696 | 0.08082505210883018 | 0.09689502904110435
FH | 0.03049505479527017 | 0.02572164644079498 | 0.3405816266189073 | 0.6282120838196407 | 0.1594062764908943 | 0.184847919598757 | 0.438925257705177 | 0.3848627640009843 | 0.306495107900996 | 0.2380136268024798 | 0.3389318855293317 | 0.3817853308770209 | 0.3963956204794078 | 0.347430097791667 | 0.1555679675768729
Z | 0.4654188925257682 | 0.07392121768610045 | 0.269937322924679 | 0.1832226633235906 | 0.1086405666634781 | 0.1847750891112773 | 0.2134863046828522 | 0.07540128491005393 | 0.1420852065199484 | 0.1725212948869104 | 0.1201882002014109 | 0.1164847306870717 | 0.04458524239365843 | 0.2058450498178954 | 0.5919378763070166
CLEAN TABLE - INDEX/ [NAME] on row 21, XLOOKUP for A22
Percentages
Other sources
 | 0.4654188925257682 | 0.07392121768610045 | 0.269937322924679 | 0.1832226633235906 | 0.1086405666634781 | 0.1847750891112773 | 0.2134863046828522 | 0.07540128491005393 | 0.1420852065199484 | 0.1725212948869104 | 0.1201882002014109 | 0.1164847306870717 | 0.04458524239365843 | 0.2058450498178954 | 0.5919378763070166
FH | 0.03049505479527017 | 0.02572164644079498 | 0.3405816266189073 | 0.6282120838196407 | 0.1594062764908943 | 0.184847919598757 | 0.438925257705177 | 0.3848627640009843 | 0.306495107900996 | 0.2380136268024798 | 0.3389318855293317 | 0.3817853308770209 | 0.3963956204794078 | 0.347430097791667 | 0.1555679675768729
SDFS | 0.03070389097681287 | 0.06722842375781528 | 0.01055215559060109 | 0.007618430653995627 | 0.0933423943096649 | 0.00397919071545598 | 0.07604647658504488 | 0.001125294224391284 | 0.06302745600060514 | 0.00249129049824536 | 0.003144245107672052 | 0.04236525803755952 | 0.1786391645205696 | 0.08082505210883018 | 0.09689502904110435
RWE | 0.07720003615550977 | 0.1383946908799795 | 0.06785582139740721 | 0.04557214409643007 | 0.0628314287085647 | 0.03750493946860104 | 0.03399124389640261 | 0.008671619712003462 | 0.1715677070181459 | 0.1042177000554847 | 0.03284518151156756 | 0.09802564923295165 | 0.1334654071577002 | 0.1778159369911378 | 0.06183103713450577
A | 0.06332708590780384 | 0.1186631666732476 | 0.119878381397331 | 0.02798206269832287 | 0.1432482181326762 | 0.03600100622059228 | 0.03062274224901136 | 0.05438453102542535 | 0.0516439900732543 | 0 | 0.08061679797875285 | 0.085926988777939 | 0.1098058874924019 | 0.03901803598905765 | 0.04121025160008022
H | 0.06463977047750077 | 0.1763804207353445 | 0.009791390975057941 | 0.006353070357527387 | 0.1489265656877632 | 0.08100226399633262 | 0.04899638759841817 | 0.1078483560331133 | 0.07964993493884992 | 0.1610183778944911 | 0.03738104262586624 | 0.007769356916436146 | 0.03875444560904738 | 0.02111645003923817 | 0.02156125986892771
Other sources | 0.1306916713254331 | 0.2416300315564304 | 0.09669453755233963 | 0.0713828130059257 | 0.1498755527082531 | 0.1647889572813165 | 0.1256757341899426 | 0.1439549378094395 | 0 | 0.2151424230555102 | 0.2366042627440177 | 0.1408687707512306 | 0.0596142421630029 | 0.1073144141499592 | 0.01623755528420053
D | 0.01791516100234117 | 0.02761063889388933 | 0.003704850639211112 | 0.02965673204456709 | 0.01533279360648877 | 0.05440319419806378 | 0.01563791163268764 | 0.02285271014705507 | 0.1855305975482003 | 0.03535313606942367 | 0.01680729768029776 | 0.01218787752714034 | 0.03825873357457717 | 0.005268182084978879 | 0.01475902318729185
B | 0.04291086301698705 | 0.06955566874196069 | 0 | 0 | 0.1005165373496684 | 0.006401852570607412 | 0.0006124548449802272 | 0.02297858684579635 | 0 | 0.07124215073745474 | 0.06211112970511097 | 0.0002960510196507307 | 0.0004812566096346063 | 0.0008710846164618434 | 0
C | 0.02225099791437046 | 0.0581955738471419 | 0.08100391290446582 | 0 | 0.01787966634254829 | 0.09604034121121043 | 0.01341276110506697 | 0.01969593158211116 | 0 | 0 | 0.0692511933663281 | 0.002135852847964769 | 0 | 0 | 0
 | 0.1306916713254331 | 0.2416300315564304 | 0.09669453755233963 | 0.0713828130059257 | 0.1498755527082531 | 0.1647889572813165 | 0.1256757341899426 | 0.1439549378094395 | 0 | 0.2151424230555102 | 0.2366042627440177 | 0.1408687707512306 | 0.0596142421630029 | 0.1073144141499592 | 0.01623755528420053
Z | 0.4654188925257682 | 0.07392121768610045 | 0.269937322924679 | 0.1832226633235906 | 0.1086405666634781 | 0.1847750891112773 | 0.2134863046828522 | 0.07540128491005393 | 0.1420852065199484 | 0.1725212948869104 | 0.1201882002014109 | 0.1164847306870717 | 0.04458524239365843 | 0.2058450498178954 | 0.5919378763070166
FH | 0.03049505479527017 | 0.02572164644079498 | 0.3405816266189073 | 0.6282120838196407 | 0.1594062764908943 | 0.184847919598757 | 0.438925257705177 | 0.3848627640009843 | 0.306495107900996 | 0.2380136268024798 | 0.3389318855293317 | 0.3817853308770209 | 0.3963956204794078 | 0.347430097791667 | 0.1555679675768729
SDFS | 0.03070389097681287 | 0.06722842375781528 | 0.01055215559060109 | 0.007618430653995627 | 0.0933423943096649 | 0.00397919071545598 | 0.07604647658504488 | 0.001125294224391284 | 0.06302745600060514 | 0.00249129049824536 | 0.003144245107672052 | 0.04236525803755952 | 0.1786391645205696 | 0.08082505210883018 | 0.09689502904110435
RWE | 0.07720003615550977 | 0.1383946908799795 | 0.06785582139740721 | 0.04557214409643007 | 0.0628314287085647 | 0.03750493946860104 | 0.03399124389640261 | 0.008671619712003462 | 0.1715677070181459 | 0.1042177000554847 | 0.03284518151156756 | 0.09802564923295165 | 0.1334654071577002 | 0.1778159369911378 | 0.06183103713450577
A | 0.06332708590780384 | 0.1186631666732476 | 0.119878381397331 | 0.02798206269832287 | 0.1432482181326762 | 0.03600100622059228 | 0.03062274224901136 | 0.05438453102542535 | 0.0516439900732543 | 0 | 0.08061679797875285 | 0.085926988777939 | 0.1098058874924019 | 0.03901803598905765 | 0.04121025160008022
H | 0.06463977047750077 | 0.1763804207353445 | 0.009791390975057941 | 0.006353070357527387 | 0.1489265656877632 | 0.08100226399633262 | 0.04899638759841817 | 0.1078483560331133 | 0.07964993493884992 | 0.1610183778944911 | 0.03738104262586624 | 0.007769356916436146 | 0.03875444560904738 | 0.02111645003923817 | 0.02156125986892771
D | 0.01791516100234117 | 0.02761063889388933 | 0.003704850639211112 | 0.02965673204456709 | 0.01533279360648877 | 0.05440319419806378 | 0.01563791163268764 | 0.02285271014705507 | 0.1855305975482003 | 0.03535313606942367 | 0.01680729768029776 | 0.01218787752714034 | 0.03825873357457717 | 0.005268182084978879 | 0.01475902318729185
A | 0.05444657590220257 | 0.002698520787295552 | 0 | 0 | 0 | 0.1502552456277857 | 0.002592725510416295 | 0.1582239837096263 | 0 | 0 | 0.00211876354964424 | 0.1121541333250347 | 0 | 0.014495696410774 | 0
B | 0.04291086301698705 | 0.06955566874196069 | 0 | 0 | 0.1005165373496684 | 0.006401852570607412 | 0.0006124548449802272 | 0.02297858684579635 | 0 | 0.07124215073745474 | 0.06211112970511097 | 0.0002960510196507307 | 0.0004812566096346063 | 0.0008710846164618434 | 0
C | 0.02225099791437046 | 0.0581955738471419 | 0.08100391290446582 | 0 | 0.01787966634254829 | 0.09604034121121043 | 0.01341276110506697 | 0.01969593158211116 | 0 | 0 | 0.0692511933663281 | 0.002135852847964769 | 0 | 0 | 0