Link to home
Start Free TrialLog in
Avatar of Tom Crowfoot
Tom CrowfootFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Place reference into Excel formula to create dynamic index

Dear Experts

I have an index formula to list out the top 10 users which works perfectly: =INDEX(SORT(S2:U123,2,-1),SEQUENCE(10),{1,2})

But the list of users changes each week so S2:U123 isn't always the full range, so I have a number formula to determine the last cell with data in the S2:Uxxx range - this also works fine in isolation ... ="S2:U"&LOOKUP(2,1/(S:S<>""),ROW(S:S)) & produces the right 'text' in a cell.  

What I'd like to be able to do is combine the two formulas so the range in the Index formula is automatically calculated.

I have tried the following but no luck:

  • Putting it directly in: =INDEX(SORT("S2:U"&LOOKUP(2,1/(S:S<>""),ROW(S:S)),2,-1),SEQUENCE(10),{1,2})
  • Referencing a futher cell (V3) to put the reuslt in: =INDEX(SORT(VALUE(V3),2,-1),SEQUENCE(10),{1,2})

Attached is an example if that helps

Please note the data in columns A-E are created by a vlookup formula (The results have been hard coded in the example) - I say this as I also wonder whether the solution might be to create the Index formula a different way so it can deal with null values from A-E thereby skipping the need to combine the formulas

EE Combining formula.xlsx

Avatar of byundt
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You could also use the INDIRECT function with the formula you were unsuccessfully trying to create:

Open in new window

As a rule, I don't like to reference entire columns in array type formulas. Doing so can sometimes make Excel recalculate slowly, and thereby result in an annoying delay each time you hit Enter.
Avatar of Tom Crowfoot


Wow thats brilliant, works a treat - thank you so much

You could also create a dynamic named range or Data Table for the source data.

Both would adjust automatically as data grows.

Dynamic Range
Use the OFFSET function to create a range "DataSource"
=OFFSET(Reference Point, Row Offset, Column Offset, Height, Width)

=OFFSET(Sheet1!$S$2,0,0,COUNTA(Sheet1!$S:$S),3)  (replace Sheet1 with sheet name)
Use reference to "DataSource" instead of "S2:S123"

Data Table
Select the existing data in columns S to U (don't select whole column or any extra rows) and press Ctrl + T, this will bring up the Table creator wizard; tick the box to confirm that the data has headers. When Table is created and cursor is within the table there will be an extra tab on the Menu Ribbon, in that tab you can rename the Table. Easiest way to include it in the formula is to put formula in edit mode, delete the reference to S2:S123 and select the table; it will show as Table1[#All]  (Table1 will show as Table Name if renamed as suggested).