Return line number

Question

Hello Excel experts,

The numbers in column J come from using a formula in this column (the formula in this column is a dynamic formula). I want, through a formula (also dynamic), to extract the numbers of the rows as given in column L starting from L3 (see the screenshot) by aligning the numbers of the columns from the largest number to the smallest number.

Keep in mind that there are repeating numbers in column J and in such a case I request that the row numbers for the repeating numbers be listed in ascending order for each of these numbers. All the numbers in column J are whole numbers (so they are not numbers with decimal points).

I want the results to be given through a dynamic formula and without using the COUNTIF function.

So is it possible to find a formula that is dynamic and that outputs the row numbers based on the above criteria?

I hope my explanation is understandable and I hope someone can help me with this.

Thank you in advance for any suggestions.

Answer 1

Try

=SORTBY(SEQUENCE(COUNT(J3#)), RANK(J3#, J3#))

Answer 2

=SORTBY(SEQUENCE(ROWS(J3#)),J3#,-1)

or

=LET(a,J3#,SORTBY(SEQUENCE(ROWS(a)),a,-1))

or

=LAMBDA(a,SORTBY(SEQUENCE(ROWS(a)),a,-1))(J3#)

You can make the third one into a named lambda formula in Name Manager using only the first part:

=LAMBDA(a,SORTBY(SEQUENCE(ROWS(a)),a,-1))

and let's say you name it LineNumbers

which would enable a simple formula like:

=LineNumbers(J3#)

on the sheet.

Answer 3

Thank you so much HansV.

It works.

Answer 4

Thank you for your help.

I appreciate it a lot.