How to read offset cells from dynamic sort array formula?

[Jay Weiss]

Hi,

I have two columns that I am working with. Column A has text labels,
and Column B has corresponding values. I would like to perform a
dynamic sort to show the value and corresponding text labels in order
from highest to lowest.

I have created an array formula to sort the values, and that works
fine:
{=LARGE(Data, ROW(INDIRECT("1:"&ROWS(Data))))}
(Note: "Data" is a named range)

The problems is that I can't seem to figure out how to pull the
corresponding text labels out of the adjacent column. It seems like I
should be able to use OFFSET to do it, but I can't seem to wrangle an
address from the sorting function to feed into OFFSET.

I'm grateful to anyone who can help.

Best wishes...

....Jay

 

[Bernd P]

Hello Jay,

LARGE does not help you if some values are appearing twice or more
often.

Put into C1:
=COUNTIF($B$1:$B$999,">"&B1)+COUNTIF($B$1:B1,B1)
Into D1:
=INDEX(A:A,MATCH(ROW(),$C$1:$C$999,))
And into E1:
=INDEX(B:B,MATCH(ROW(),$C$1:$C$999,))
and copy down as far as necessary.

Hope that helps,
Bernd