troubleshooting Question

# Place reference into Excel formula to create dynamic index

Microsoft ExcelMicrosoft Office
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

###### Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.