troubleshooting Question

Place reference into Excel formula to create dynamic index

Avatar of Tom Crowfoot
Tom CrowfootFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft ExcelMicrosoft Office
4 Comments1 Solution9 ViewsLast Modified:
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


ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
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.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros