SORTBY function where Lookup Array is determined by Match (Excel)

Question

I have a large dataset with differing versions and scenarios, which are denoted in column headers. On a separate tab I am trying to pull back the top 10 account names with unfavorable variances and top 10 favorable variances, but I want the formula to be dynamic where it will adjust depending on the division, time period and forecast version that are selected. I have tried SORT(FILTER(), SORTBY using concatenating to come up with the address, but nothing seems to work...(examples below). Any thoughts on how to modify these formulas to return the sorted accounts?

{=SORTBY('Sheet 2'!$D$14:$D$119,"'Sheet 2!$"&SUBSTITUTE(ADDRESS(1,MATCH(C5&D7&C4&E8,'Sheet 2'!$4:$4,0),4),"1","")&"$14:$"&SUBSTITUTE(ADDRESS(1,MATCH(C5&D7&C4&E8,'Sheet 2'!$4:$4,0),4),"1","")&"$119",-1)}

=SORT(FILTER('Sheet 2'!$DK$14:$ER$119,MATCH(C5&D7&C4&E8,'Sheet 2'!$4:$4,0)),,1)

The return range is static, but the lookup array can change based on criteria. In the first formula I tried to point the lookup array to the appropriate tab, column and cell range. The second SORT formula was a shot in the dark...

Data view where column D is the return array and any of the columns between G and AN could be the lookup array - relevant sorting rows 14:119

Answer 1

I am not quite sure what you want to do. But I think you need to use INDIRECT to convert an address string into a lookup range, something like below:

=INDIRECT("'Sheet 2'!"&ADDRESS(1,1,1,1)&":"&ADDRESS(5,6,1,1))